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.
CREATE PROCEDURE GenerateRandomString @sLength tinyint , -- LENGTH OF STRING TO GENERATE @RandomString nvarchar(MAX) OUTPUT -- RETURNED RANDOM STRING AS SET NOCOUNT ON DECLARE @Count tinyint, @NextC char(1) SET @Count = 1 SET @RandomString = '' WHILE @Count <= @sLength BEGIN SELECT @NextC = CHAR(ROUND(RAND() * 81 + 33, 0)) IF ASCII(@NextC) not IN (34, 39, 40, 41, 44, 46, 96, 58, 59) AND ASCII(@NextC) < 123 AND ASCII(@NextC) > 48 BEGIN SELECT @RandomString = @RandomString + @NextC SET @Count = @Count + 1 END END GO
To execute the stored procedure we can make the following call,
DECLARE @RandomString NVARCHAR(MAX) EXEC GenerateRandomString 10, @RandomString OUTPUT
The random string or password output from the stored procedure will be different each time. You can exclude any ASCII character that you require by entering the ASCII code into the
IN (34, 39, 40, 41, 44, 46, 96, 58, 59)
For a good ASCII table with symbols and codes then check out the website ASCiiTable.com