DBTechie

I needed to create a user-defined function (UDF) in SQL Server that returned a random password.  My initial idea was to generate a random integer between some ASCII value ‘x’ and ASCII value ‘y,’ then concatenate several of these together and voila.  Upon looking at the ASCII chart, I realized that there was no acceptable “block” of values that I wanted to use.  I did not want to use punctuation marks or certain characters that are easily confused, such as: 0,o,O,l,1,i,I, etc.   Additionally, to prevent an obscene word from being randomly created, I wanted to eliminate all vowels.  So, my next thought was to create a look-up, cross reference table where a random ID value would correspond to a character in the table; however, this seemed like quite a bit of overhead for a simple user-defined function.  Finally, I decided to use a single text variable to hold all of the desired characters.  Then, I pulled random characters out of the variable base on the characters’ relative position inside the string of text. 

The T-SQL is as follows:

Note that the following UDF (fn_GetRandomPassword) uses the function MyRand() previously discussed here:

http://www.dbtechie.com/sql-server/user-defined-functions-udf/random-thoughts-on-sql-server/

 

– =============================================

– Description:      Create a random password of PasswordLength

–    comprised of lowercase letters, not including [a,e,i,j,l,o,u] and

–    including the numbers 2 through 8.  Maximum length = 100 chars.

– =============================================

CREATE FUNCTION [dbo].[fn_GetRandomPassword]

(

       @PasswordLength INT

)

RETURNS varchar(100)

AS

BEGIN

       DECLARE @retVal VARCHAR(100)

       DECLARE @charList VARCHAR(26)

       DECLARE @rnd FLOAT

       DECLARE @loopCounter INT

       DECLARE @randID INT

       DECLARE @oneChar CHAR(1)

 

 

       – build the charList with chars a-z

       SET @charList = ”

       SET @loopCounter=97

       WHILE @loopCounter<123

       BEGIN – while

              SET @charList = @charList + CHAR(@loopCounter)

              SET @loopCounter = @loopCounter + 1

       END –while

 

 

       –replace unwanted characters with numbers 2-8

 

       –replace a

       SET @charList = REPLACE(@charList,‘a’,‘2′)

 

       –replace e

       SET @charList = REPLACE(@charList,‘e’,‘3′)

 

       –replace i

       SET @charList = REPLACE(@charList,‘i’,‘4′)

 

       –replace j

       SET @charList = REPLACE(@charList,‘j’,‘5′)

 

       –replace l

       SET @charList = REPLACE(@charList,‘l’,‘6′)

 

       –replace o

       SET @charList = REPLACE(@charList,‘o’,‘7′)

 

       –replace u

       SET @charList = REPLACE(@charList,‘u’,‘8′)

      

       – force passwordLength to be a legitimate value

       IF @PasswordLength >100

              SET @PasswordLength=100

 

       IF @PasswordLength <1

              SET @PasswordLength =1

      

 

       – build random password

       SET @loopCounter=0

       SET @retVal = ”

 

       WHILE @loopCounter < @PasswordLength

       BEGIN – while

 

              SET @rnd = (Select dbo.MyRand())  –Get random float from 0-1

 

              SET @randID = ROUND(25 * @rnd,0) + 1   –Get random int from 1 to 26

              SET @oneChar = SUBSTRING(@charList,@randID,1)

              SET @retVal = @retVal + @oneChar

              SET @loopCounter = @loopCounter + 1

       END – while

 

       RETURN @retVal

 

END