Creating Random Strings in SQL Server

The Challenge

I want to be able to create a random string in SQL Server.

The Solution

This solution is presented in three parts. The first part is the ability to select a random integer between two boundary values. We need to pass in our random value, since SQL does not allow non-determinant functions (e.g. RAND()) to be called from within user-defined functions. As usual, we will also pass in our lower and upper bounds.

CREATE FUNCTION [dbo].[fn_RandIntBetween]
(
  @lower  INT,
  @upper  INT,
  @rand   FLOAT
)
RETURNS INT
AS
BEGIN
  DECLARE @result INT;
  DECLARE @range = @upper - @lower + 1;
  SET @result = FLOOR(@rand * @range + @lower);
  RETURN @result;
END
GO

The next part is to create a function to pick a random character from a given list of characters. Picking a random character will use the function we created above.

CREATE FUNCTION [dbo].[fn_PickRandomChar]
(
  @chars VARCHAR(MAX),
  @rand  FLOAT
)
RETURNS CHAR(1)
AS
BEGIN
  DECLARE @result CHAR(1) = NULL;
  DECLARE @resultIndex INT = NULL;
  IF @chars IS NULL
    SET @result = NULL;
  ELSE IF LEN(@chars) = 0
    SET @result = NULL
  ELSE
  BEGIN
    SET @resultIndex = [dbo].[fn_RandIntBetween](1, LEN(@chars), @rand);
    SET @result = SUBSTRING(@chars, @resultIndex, 1);
  END

  RETURN @result;
END

We can use this function as follows.

DECLARE @rchar CHAR(1);
SELECT [dbo].[fn_PickRandomChar]('abcdefghijklmnopqrstuvwxyz', RAND()) as [Random Char];

-- Results:
-- Random Char
-- -----------
-- v

Pretty good, no?

Our last step is to create a stored procedure to generate a random string. Note: this must be a stored procedure. Because SQL Server does not allow RAND() in a user-defined function, we’ll put our operation in a procedure.

CREATE PROCEDURE [dbo].[CreateRandomString]
  @minLength INT = 1,
  @maxLength INT = 50,
  @chars VARCHAR(200) = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
  @randomString VARCHAR(MAX) = NULL OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  -- Get the length of our string.
  DECLARE @stringLength INT = [dbo].[fn_RandIntBetween](@minLength, @maxLength, RAND());

  -- Set our random string to an empty string.
  SET @randomString = '';

  -- If our string is not yet the appropriate length, add another character to the string.
  WHILE LEN(@randomString) < @stringLength
  BEGIN
    SET @randomString = @randomString + [dbo].[fn_PickRandomChar](@chars, RAND());
  END

END

Now we have all of the tools we need to create a random string. Let’s put it all together and see how this works.

DECLARE @minLength INT = 5;
DECLARE @maxLength INT = 20;
DECLARE @chars VARCHAR(26) = 'abcdefghijklmnopqrstuvwxyz';
DECLARE @text VARCHAR(50) = NULL;

EXEC [dbo].[CreateRandomString] @minLength = @minLength,
                                @maxLength = @maxLength,
                                @chars = @chars,
                                @randomString = @text OUTPUT;
SELECT @randomString AS [Random String];

-- Results:
-- Random String
-- -------------
-- bcuwjolzrwk

Happy to help!