So how can we generate a random string with TSQL? Below is a SQL stored procedure that I use when needing to generating completely random strings which is particularly useful and usually a requirement when dealing with user accounts in our applications.

You are able to set how long you would like the random string or password and then the code just does a simple loop and calls the RAND function to generate a random number and turns that number into a CHAR value.

We next check to see is the CHAR value that has been generated is a CHAR that we actually want, for example we would not want a carriage return or line feed character to form part of our string.

We keep looping until we have made the string the required length and then we return the newly created string into the output parameter @RandomString.

Lots of systems hold alphanumeric strings as record identifiers, for example order numbers, serial numbers, employee numbers, etc… Mostly these record keys will consist of a prefix which is an alpha character and then a number such as “ABC0000123”.

Sometimes we have the situation where we need to split the alpha and numeric parts up into two separate variables whether this is for incrementing the number to get the next number in the sequence or we may need to find out what the prefix of the number is.

The two functions below do just this, one will return the prefix the other will return the number part.

Function GetNumber(ByVal sNumberString AsString)AsInteger

Dim sNew AsString=""

For i AsInteger=0To sNumberString.Length-1

IfIsNumeric(sNumberString.Substring(i, 1))=TrueThen

sNew += sNumberString.Substring(i, 1)

EndIf

Next

IfString.IsNullOrEmpty(sNew)Then sNew ="0"

ReturnCInt(sNew)

EndFunction

Function GetPrefix(ByVal sNumberString AsString)AsString

Dim sNew AsString=""

For i AsInteger=0To sNumberString.Length-1

IfIsNumeric(sNumberString.Substring(i, 1))=FalseThen

sNew += sNumberString.Substring(i, 1)

Else

ExitFor

EndIf

Next

Return sNew

EndFunction

The GetNumber function accepts a string as an input and returns a integer which forms the number part of the string is in the example of our order number “ABC0000123” the function would return 123.

The GetPrefix function accepts the same string as an input but returns a string which forms our prefix part so again in the example our order number “ABC0000123” would return ABC.