Monday, December 29, 2008

t-sql random string generator

This is a simple stored procedure that you can use to generate a random string. You can use it as a random password generator and for other purposes. It is very simple so I won't bore you with unnecessary explanations but if you do happen to have any questions feel free to post them here.

CREATE PROCEDURE [dbo].[usp_GenerateRandomString]
  @sLength tinyint = 10,
  @randomString varchar(50) OUTPUT
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @counter tinyint
  DECLARE @nextChar char(1)
  SET @counter = 1
  SET @randomString = ''

  WHILE @counter <= @sLength
    BEGIN
      SELECT @nextChar = CHAR(ROUND(RAND() * 93 + 33, 0))
      IF ASCII(@nextChar) not in (34, 39, 40, 41, 44, 46, 96, 58, 59)
        BEGIN
          SELECT @randomString = @randomString + @nextChar
          SET @counter = @counter + 1
        END
    END
END

There are of course other ways to accomplish this – a very simple one would be to use newid() and grab whatever number of characters you need from it.

1 comment:

Paul said...

Many thanks!

I've done something similar to randomize existing data to help me obfuscate information.

Check out http://www.selectsystems.ca/blog/post/Randomize-Data-in-a-SQL-Table-Column.aspx.

Paul