Generating random strings with T-SQL

Question

If you wanted to generate a pseudorandom alphanumeric string using T-SQL, how would you do it? How would you exclude characters like dollar signs, dashes, and slashes from it?

Answer 1

Lorem Ipsum:

You can randomize lorem ipsum words like this, I used a dictionary of 64 words:

SELECT STRING_AGG(text, ' ')
FROM (
    SELECT top(64) text
    FROM (
        VALUES ('lorem'), ('ipsum'), ('dolor'), ('sit'), ('amet,'), ('consectetur'), ('adipiscing'), ('elit,'), ('sed'), ('do'), ('eiusmod'), ('tempor'), ('incididunt'), ('ut'), ('labore'), ('et'), ('dolore'), ('magna'), ('aliqua.'), ('ut'), ('enim'), ('ad'), ('minim'), ('veniam,'), ('quis'), ('nostrud'), ('exercitation'), ('ullamco'), ('laboris'), ('nisi'), ('aliquip'), ('ex'), ('ea'), ('commodo'), ('consequat.'), ('duis'), ('aute'), ('irure'), ('in'), ('reprehenderit'), ('voluptate'), ('velit'), ('esse'), ('cillum'), ('eu'), ('fugiat'), ('nulla'), ('pariatur.'), ('excepteur'), ('sint'), ('occaecat'), ('cupidatat'), ('non'), ('proident,'), ('sunt'), ('culpa'), ('qui'), ('officia'), ('deserunt'), ('mollit'), ('anim'), ('id'), ('est'), ('laborum')
    ) AS a(text)
    ORDER BY NEWID()
) t

This will return a different lorem ipsum string each time you run that query, later on, if you want to update multiple rows you'll have to create a function:

CREATE VIEW getNewID as select newid() as new_id;

CREATE OR ALTER FUNCTION dbo.lorem_ipsum(@length bigint) RETURNS NVARCHAR(max) AS
BEGIN
    DECLARE @return NVARCHAR(MAX)
    SELECT @return = STRING_AGG(text, ' ')
    FROM (
        SELECT top(64) text
        FROM (
            VALUES ('lorem'), ('ipsum'), ('dolor'), ('sit'), ('amet,'), ('consectetur'), ('adipiscing'), ('elit,'), ('sed'), ('do'), ('eiusmod'), ('tempor'), ('incididunt'), ('ut'), ('labore'), ('et'), ('dolore'), ('magna'), ('aliqua.'), ('ut'), ('enim'), ('ad'), ('minim'), ('veniam,'), ('quis'), ('nostrud'), ('exercitation'), ('ullamco'), ('laboris'), ('nisi'), ('aliquip'), ('ex'), ('ea'), ('commodo'), ('consequat.'), ('duis'), ('aute'), ('irure'), ('in'), ('reprehenderit'), ('voluptate'), ('velit'), ('esse'), ('cillum'), ('eu'), ('fugiat'), ('nulla'), ('pariatur.'), ('excepteur'), ('sint'), ('occaecat'), ('cupidatat'), ('non'), ('proident,'), ('sunt'), ('culpa'), ('qui'), ('officia'), ('deserunt'), ('mollit'), ('anim'), ('id'), ('est'), ('laborum')
        ) AS a(text), getNewID
        
        ORDER BY new_id
    ) t
    return upper(substring(@return, 1, 1)) + substring(@return, 2, @length - 1);
END

And then update all your values:

UPDATE my_custom_table
SET my_text_column = dbo.lorem_ipsum(len(my_text_column))

Answer 2

Another simple solution with the complete alphabet:

SELECT LEFT(REPLACE(REPLACE((SELECT CRYPT_GEN_RANDOM(16) FOR XML PATH(''), BINARY BASE64),'+',''),'/',''),16);

Replace the two 16's with the desired length.

Sample result:

pzyMATe3jJwN1XkB

[Edit 2024] For me, this result is valid because the probability of having many '+' and '/' characters in a random number is extremely low, almost negligible; but if you want to be certain that you always have X (16 here) in length, even for low values of X, you can do the following:

SELECT LEFT(REPLACE(REPLACE(REPLACE((SELECT CRYPT_GEN_RANDOM(16) FOR XML PATH(''), BINARY BASE64), '+', ''), '/', ''), '=', '') + REPLACE(NEWID(), '-', ''), 16);

Answer 3

CREATE 
OR ALTER PROC USP_GENERATE_RANDOM_CHARACTER (
  @NO_OF_CHARS INT, 
  @RANDOM_CHAR VARCHAR(40) OUTPUT
) AS BEGIN 
SELECT 
  @RANDOM_CHAR = SUBSTRING (
    REPLACE(
      CONVERT(
        VARCHAR(40), 
        NEWID()
      ), 
      '-', 
      ''
    ), 
    1, 
    @NO_OF_CHARS
  ) END

USAGE:

DECLARE @OUT VARCHAR(40)
EXEC USP_GENERATE_RANDOM_CHARACTER 13,@RANDOM_CHAR = @OUT  OUTPUT
SELECT @OUT

Answer 4

If you want to generate random alphanumeric strings with a character set you can do the following:

1. Choose a characterset
2. generate randomnumber >= the length of characterset (1)
3. Pick one single character using the randomnumber generated in (2) and pick that character from the sequence of characterset
4. Append that character you got from (3) to the resultset
5. Check the length of the resultset if less then desired the repeat from (2)

In the below example I am doing the same, the code is well commented to understand.

-- random string generator
declare @characterSet varchar(52) = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ' --character set
declare @randomString nvarchar(10) -- the size should be >= the max length required
declare @onechar nvarchar(1) -- one character
declare @i int = 0; -- counter
declare @r int = 0; --random int


While @i <= 9 -- put (max length - 1) here it's 10
    begin
        set @r = floor(rand() * LEN(@characterSet))
        set @onechar = SUBSTRING(@characterSet, @r,1)
        set @randomString = concat(@randomString, @onechar); 
        set @i = len(@randomString)
    end


-- select randon string
select  @randomString

And the result will be a 10 character long alphanumeric string something like:

o0p7K6Jvbv
jFdbe0t5cy
12rpqkFjpA
LwAC2n7Jk4
53lA17buuc

---

This will be another example as how to create string of random lower & upper characters in two different columns. It's using a while loop which can be controlled to generate the string with a specific length.

--random string generator
declare @maxLength int = 5; --max length
declare @bigstr varchar(10) --uppercase character
declare @smallstr varchar(10) --lower character
declare @i int = 1; 
While @i <= @maxLength
    begin
        set @bigstr = concat(@bigstr, char((rand()*26 + 65))); 
        set @smallstr = concat(@smallstr, char((rand()*26 + 96))); 
        set @i = len(@bigstr)
    end

--select query
select @bigstr, @smallstr

---

Answer 5

Small modification of Remus Rusanu code -thanks for sharing

This generate a random string and can be used without the seed value

   declare @minLen int = 1, @maxLen int = 612, @string varchar(8000);

declare @length int; 
declare @seed INT 
declare @alpha varchar(8000)
    , @digit varchar(8000)
    , @specials varchar(8000)
    , @first varchar(8000)
declare @step bigint = rand() * 2147483647;


select @alpha = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
    , @digit = '1234567890'
    , @specials = '_@#-/\ '
select @first = @alpha + '_@';

set  @seed = (rand(@step)*2147483647);


select @length = @minLen + rand(@seed) * (@maxLen-@minLen)
    , @seed = (rand((@seed+@step)%2147483647)*2147483647);



declare @dice int;
select @dice = rand(@seed) * len(@first),
    @seed = (rand((@seed+@step)%2147483647)*2147483647);
select @string = substring(@first, @dice, 1);

while 0 < @length 
begin
    select @dice = rand(@seed) * 100
        , @seed = (rand((@seed+@step)%2147483647)*2147483647);
    if (@dice < 10) -- 10% special chars
    begin
        select @dice = rand(@seed) * len(@specials)+1
            , @seed = (rand((@seed+@step)%2147483647)*2147483647);
        select @string = @string + substring(@specials, @dice, 1);
    end
    else if (@dice < 10+10) -- 10% digits
    begin
        select @dice = rand(@seed) * len(@digit)+1
            , @seed = (rand((@seed+@step)%2147483647)*2147483647);
        select @string = @string + substring(@digit, @dice, 1);
    end
    else -- rest 80% alpha
    begin
        declare @preseed int = @seed;
        select @dice = rand(@seed) * len(@alpha)+1
            , @seed = (rand((@seed+@step)%2147483647)*2147483647);

        select @string = @string + substring(@alpha, @dice, 1);
    end

    select @length = @length - 1

    end 
    SELECT @string 

Answer 6

So I liked a lot of the answers above, but I was looking for something that was a little more random in nature. I also wanted a way to explicitly call out excluded characters. Below is my solution using a view that calls the CRYPT_GEN_RANDOM to get a cryptographic random number. In my example, I only chose a random number that was 8 bytes. Please note, you can increase this size and also utilize the seed parameter of the function if you want. Here is the link to the documentation: https://learn.microsoft.com/en-us/sql/t-sql/functions/crypt-gen-random-transact-sql

CREATE VIEW [dbo].[VW_CRYPT_GEN_RANDOM_8]
AS
SELECT CRYPT_GEN_RANDOM(8) as [value];

The reason for creating the view is because CRYPT_GEN_RANDOM cannot be called directly from a function.

From there, I created a scalar function that accepts a length and a string parameter that can contain a comma delimited string of excluded characters.

CREATE FUNCTION [dbo].[fn_GenerateRandomString]
( 
    @length INT,
    @excludedCharacters VARCHAR(200) --Comma delimited string of excluded characters
)
RETURNS VARCHAR(Max)
BEGIN
    DECLARE @returnValue VARCHAR(Max) = ''
        , @asciiValue INT
        , @currentCharacter CHAR;

    --Optional concept, you can add default excluded characters
    SET @excludedCharacters = CONCAT(@excludedCharacters,',^,*,(,),-,_,=,+,[,{,],},\,|,;,:,'',",<,.,>,/,`,~');
    
    --Table of excluded characters
    DECLARE @excludedCharactersTable table([asciiValue] INT);

    --Insert comma
    INSERT INTO @excludedCharactersTable SELECT 44;

    --Stores the ascii value of the excluded characters in the table
    INSERT INTO @excludedCharactersTable
    SELECT ASCII(TRIM(value))
    FROM STRING_SPLIT(@excludedCharacters, ',')
    WHERE LEN(TRIM(value)) = 1;

    --Keep looping until the return string is filled
    WHILE(LEN(@returnValue) < @length)
    BEGIN
        --Get a truly random integer values from 33-126
        SET @asciiValue = (SELECT TOP 1 (ABS(CONVERT(INT, [value])) % 94) + 33 FROM [dbo].[VW_CRYPT_GEN_RANDOM_8]);

        --If the random integer value is not in the excluded characters table then append to the return string
        IF(NOT EXISTS(SELECT * 
                        FROM @excludedCharactersTable 
                        WHERE [asciiValue] = @asciiValue))
        BEGIN
            SET @returnValue = @returnValue + CHAR(@asciiValue);
        END
    END

    RETURN(@returnValue);
END

Below is an example of the how to call the function.

SELECT [dbo].[fn_GenerateRandomString](8,'!,@,#,$,%,&,?');

Answer 7

Similar to the first example, but with more flexibility:

-- min_length = 8, max_length = 12
SET @Length = RAND() * 5 + 8
-- SET @Length = RAND() * (max_length - min_length + 1) + min_length

-- define allowable character explicitly - easy to read this way an easy to 
-- omit easily confused chars like l (ell) and 1 (one) or 0 (zero) and O (oh)
SET @CharPool = 
    'abcdefghijkmnopqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ23456789.,-_!$@#%^&*'
SET @PoolLength = Len(@CharPool)

SET @LoopCount = 0
SET @RandomString = ''

WHILE (@LoopCount < @Length) BEGIN
    SELECT @RandomString = @RandomString + 
        SUBSTRING(@Charpool, CONVERT(int, RAND() * @PoolLength) + 1, 1)
    SELECT @LoopCount = @LoopCount + 1
END

I forgot to mention one of the other features that makes this more flexible. By repeating blocks of characters in @CharPool, you can increase the weighting on certain characters so that they are more likely to be chosen.

Answer 8

Below are the way to Generate 4 Or 8 Characters Long Random Alphanumeric String in SQL

select LEFT(CONVERT(VARCHAR(36),NEWID()),4)+RIGHT(CONVERT(VARCHAR(36),NEWID()),4)

---

 SELECT RIGHT(REPLACE(CONVERT(VARCHAR(36),NEWID()),'-',''),8)

Answer 9

Based on various helpful responses in this article I landed with a combination of a couple options I liked.

DECLARE @UserId BIGINT = 12345 -- a uniqueId in my system
SELECT LOWER(REPLACE(NEWID(),'-','')) + CONVERT(VARCHAR, @UserId)

Answer 10

For SQL Server 2016 and later, here is a really simple and relatively efficient expression to generate cryptographically random strings of a given byte length:

--Generates 36 bytes (48 characters) of base64 encoded random data
select r from OpenJson((select Crypt_Gen_Random(36) r for json path)) 
  with (r varchar(max))

Note that the byte length is not the same as the encoded size; use the following from this article to convert:

Bytes = 3 * (LengthInCharacters / 4) - Padding

Answer 11

This will produce a string 96 characters in length, from the Base64 range (uppers, lowers, numbers, + and /). Adding 3 "NEWID()" will increase the length by 32, with no Base64 padding (=).

    SELECT 
        CAST(
            CONVERT(NVARCHAR(MAX),
                CONVERT(VARBINARY(8), NEWID())
                +CONVERT(VARBINARY(8), NEWID())
                +CONVERT(VARBINARY(8), NEWID())
                +CONVERT(VARBINARY(8), NEWID())
                +CONVERT(VARBINARY(8), NEWID())
                +CONVERT(VARBINARY(8), NEWID())
                +CONVERT(VARBINARY(8), NEWID())
                +CONVERT(VARBINARY(8), NEWID())
                +CONVERT(VARBINARY(8), NEWID())
            ,2) 
        AS XML).value('xs:base64Binary(xs:hexBinary(.))', 'VARCHAR(MAX)') AS StringValue

If you are applying this to a set, make sure to introduce something from that set so that the NEWID() is recomputed, otherwise you'll get the same value each time:

  SELECT 
    U.UserName
    , LEFT(PseudoRandom.StringValue, LEN(U.Pwd)) AS FauxPwd
  FROM Users U
    CROSS APPLY (
        SELECT 
            CAST(
                CONVERT(NVARCHAR(MAX),
                    CONVERT(VARBINARY(8), NEWID())
                    +CONVERT(VARBINARY(8), NEWID())
                    +CONVERT(VARBINARY(8), NEWID())
                    +CONVERT(VARBINARY(8), NEWID())
                    +CONVERT(VARBINARY(8), NEWID())
                    +CONVERT(VARBINARY(8), NEWID())
                    +CONVERT(VARBINARY(8), NEWID())
                    +CONVERT(VARBINARY(8), NEWID())
                    +CONVERT(VARBINARY(8), NEWID())
                    +CONVERT(VARBINARY(8), NEWID())
                    +CONVERT(VARBINARY(8), U.UserID)  -- Causes a recomute of all NEWID() calls
                ,2) 
            AS XML).value('xs:base64Binary(xs:hexBinary(.))', 'VARCHAR(MAX)') AS StringValue
    ) PseudoRandom

Answer 12

it's very simply.use it and enjoy.

CREATE VIEW [dbo].[vwGetNewId]
AS
SELECT        NEWID() AS Id

Creat FUNCTION [dbo].[fnGenerateRandomString](@length INT = 8)
RETURNS NVARCHAR(MAX)
AS
BEGIN

DECLARE @result CHAR(2000);

DECLARE @String VARCHAR(2000);

SET @String = 'abcdefghijklmnopqrstuvwxyz' + --lower letters
'ABCDEFGHIJKLMNOPQRSTUVWXYZ' + --upper letters
'1234567890'; --number characters

SELECT @result =
(
    SELECT TOP (@length)
           SUBSTRING(@String, 1 + number, 1) AS [text()]
    FROM master..spt_values
    WHERE number < DATALENGTH(@String)
          AND type = 'P'
    ORDER BY
(
    SELECT TOP 1 Id FROM dbo.vwGetNewId
)   --instead of using newid()
    FOR XML PATH('')
);

RETURN @result;

END;

Answer 13

Here's one I came up with today (because I didn't like any of the existing answers enough).

This one generates a temp table of random strings, is based off of newid(), but also supports a custom character set (so more than just 0-9 & A-F), custom length (up to 255, limit is hard-coded, but can be changed), and a custom number of random records.

Here's the source code (hopefully the comments help):

/**
 * First, we're going to define the random parameters for this
 * snippet. Changing these variables will alter the entire
 * outcome of this script. Try not to break everything.
 *
 * @var {int}       count    The number of random values to generate.
 * @var {int}       length   The length of each random value.
 * @var {char(62)}  charset  The characters that may appear within a random value.
 */

-- Define the parameters
declare @count int = 10
declare @length int = 60
declare @charset char(62) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'

/**
 * We're going to define our random table to be twice the maximum
 * length (255 * 2 = 510). It's twice because we will be using
 * the newid() method, which produces hex guids. More later.
 */

-- Create the random table
declare @random table (
    value nvarchar(510)
)

/**
 * We'll use two characters from newid() to make one character in
 * the random value. Each newid() provides us 32 hex characters,
 * so we'll have to make multiple calls depending on length.
 */

-- Determine how many "newid()" calls we'll need per random value
declare @iterations int = ceiling(@length * 2 / 32.0)

/**
 * Before we start making multiple calls to "newid", we need to
 * start with an initial value. Since we know that we need at
 * least one call, we will go ahead and satisfy the count.
 */

-- Iterate up to the count
declare @i int = 0 while @i < @count begin set @i = @i + 1

    -- Insert a new set of 32 hex characters for each record, limiting to @length * 2
    insert into @random
        select substring(replace(newid(), '-', ''), 1, @length * 2)

end

-- Now fill the remaining the remaining length using a series of update clauses
set @i = 0 while @i < @iterations begin set @i = @i + 1

    -- Append to the original value, limit @length * 2
    update @random
        set value = substring(value + replace(newid(), '-', ''), 1, @length * 2)

end

/**
 * Now that we have our base random values, we can convert them
 * into the final random values. We'll do this by taking two
 * hex characters, and mapping then to one charset value.
 */

-- Convert the base random values to charset random values
set @i = 0 while @i < @length begin set @i = @i + 1

    /**
     * Explaining what's actually going on here is a bit complex. I'll
     * do my best to break it down step by step. Hopefully you'll be
     * able to follow along. If not, then wise up and come back.
     */

    -- Perform the update
    update @random
        set value =

            /**
             * Everything we're doing here is in a loop. The @i variable marks
             * what character of the final result we're assigning. We will
             * start off by taking everything we've already done first.
             */

            -- Take the part of the string up to the current index
            substring(value, 1, @i - 1) +

            /**
             * Now we're going to convert the two hex values after the index,
             * and convert them to a single charset value. We can do this
             * with a bit of math and conversions, so function away!
             */

            -- Replace the current two hex values with one charset value
            substring(@charset, convert(int, convert(varbinary(1), substring(value, @i, 2), 2)) * (len(@charset) - 1) / 255 + 1, 1) +
    --  (1) -------------------------------------------------------^^^^^^^^^^^^^^^^^^^^^^^-----------------------------------------
    --  (2) ---------------------------------^^^^^^^^^^^^^^^^^^^^^^11111111111111111111111^^^^-------------------------------------
    --  (3) --------------------^^^^^^^^^^^^^2222222222222222222222222222222222222222222222222^------------------------------------
    --  (4) --------------------333333333333333333333333333333333333333333333333333333333333333---^^^^^^^^^^^^^^^^^^^^^^^^^--------
    --  (5) --------------------333333333333333333333333333333333333333333333333333333333333333^^^4444444444444444444444444--------
    --  (6) --------------------5555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555^^^^----
    --  (7) ^^^^^^^^^^^^^^^^^^^^66666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666^^^^

            /**
             * (1) - Determine the two hex characters that we'll be converting (ex: 0F, AB, 3C, etc.)
             * (2) - Convert those two hex characters to a a proper hexadecimal (ex: 0x0F, 0xAB, 0x3C, etc.)
             * (3) - Convert the hexadecimals to integers (ex: 15, 171, 60)
             * (4) - Determine the conversion ratio between the length of @charset and the range of hexadecimals (255)
             * (5) - Multiply the integer from (3) with the conversion ratio from (4) to get a value between 0 and (len(@charset) - 1)
             * (6) - Add 1 to the offset from (5) to get a value between 1 and len(@charset), since strings start at 1 in SQL
             * (7) - Use the offset from (6) and grab a single character from @subset
             */

            /**
             * All that is left is to add in everything we have left to do.
             * We will eventually process the entire string, but we will
             * take things one step at a time. Round and round we go!
             */

            -- Append everything we have left to do
            substring(value, 2 + @i, len(value))

end

-- Select the results
select value
from @random

It's not a stored procedure, but it wouldn't be that hard to turn it into one. It's also not horrendously slow (it took me ~0.3 seconds to generate 1,000 results of length 60, which is more than I'll ever personally need), which was one of my initial concerns from all of the string mutation I'm doing.

The main takeaway here is that I'm not trying to create my own random number generator, and my character set isn't limited. I'm simply using the random generator that SQL has (I know there's rand(), but that's not great for table results). Hopefully this approach marries the two kinds of answers here, from overly simple (i.e. just newid()) and overly complex (i.e. custom random number algorithm).

It's also short (minus the comments), and easy to understand (at least for me), which is always a plus in my book.

However, this method cannot be seeded, so it's going to be truly random each time, and you won't be able to replicate the same set of data with any means of reliability. The OP didn't list that as a requirement, but I know that some people look for that sort of thing.

I know I'm late to the party here, but hopefully someone will find this useful.

Answer 14

For one random letter, you can use:

select substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ',
                 (abs(checksum(newid())) % 26)+1, 1)

An important difference between using newid() versus rand() is that if you return multiple rows, newid() is calculated separately for each row, while rand() is calculated once for the whole query.

Answer 15

In SQL Server 2012+ we could concatenate the binaries of some (G)UIDs and then do a base64 conversion on the result.

SELECT 
    textLen.textLen
,   left((
        select  CAST(newid() as varbinary(max)) + CAST(newid() as varbinary(max)) 
        where   textLen.textLen is not null /*force evaluation for each outer query row*/ 
        FOR XML PATH(''), BINARY BASE64
    ),textLen.textLen)   as  randomText
FROM ( values (2),(4),(48) ) as textLen(textLen)    --define lengths here
;

If you need longer strings (or you see = characters in the result) you need to add more + CAST(newid() as varbinary(max)) in the sub select.

Answer 16

Sometimes we need a lot of random things: love, kindness, vacation, etc. I have collected a few random generators over the years, and these are from Pinal Dave and a stackoverflow answer I found once. Refs below.

--Adapted from Pinal Dave; http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/
SELECT 
    ABS( CAST( NEWID() AS BINARY( 6)) %1000) + 1 AS RandomInt
    , CAST( (ABS( CAST( NEWID() AS BINARY( 6)) %1000) + 1)/7.0123 AS NUMERIC( 15,4)) AS RandomNumeric
    , DATEADD( DAY, -1*(ABS( CAST( NEWID() AS BINARY( 6)) %1000) + 1), GETDATE()) AS RandomDate
    --This line from http://stackoverflow.com/questions/15038311/sql-password-generator-8-characters-upper-and-lower-and-include-a-number
    , CAST((ABS(CHECKSUM(NEWID()))%10) AS VARCHAR(1)) + CHAR(ASCII('a')+(ABS(CHECKSUM(NEWID()))%25)) + CHAR(ASCII('A')+(ABS(CHECKSUM(NEWID()))%25)) + LEFT(NEWID(),5) AS RandomChar
    , ABS(CHECKSUM(NEWID()))%50000+1 AS RandomID

Answer 17

I use this procedure that I developed simply stipluate the charaters you want to be able to display in the input variables, you can define the length too. Hope this formats well, I am new to stack overflow.

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND object_id = OBJECT_ID(N'GenerateARandomString'))
DROP PROCEDURE GenerateARandomString
GO

CREATE PROCEDURE GenerateARandomString
(
     @DESIREDLENGTH         INTEGER = 100,                  
     @NUMBERS               VARCHAR(50) 
        = '0123456789',     
     @ALPHABET              VARCHAR(100) 
        ='ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
     @SPECIALS              VARCHAR(50) 
        = '_=+-$£%^&*()"!@~#:', 
     @RANDOMSTRING          VARCHAR(8000)   OUT 

)

AS

BEGIN
    -- Author David Riley
    -- Version 1.0 
    -- You could alter to one big string .e.e numebrs , alpha special etc
    -- added for more felxibility in case I want to extend i.e put logic  in for 3 numbers, 2 pecials 3 numbers etc
    -- for now just randomly pick one of them

    DECLARE @SWAP                   VARCHAR(8000);      -- Will be used as a tempoary buffer 
    DECLARE @SELECTOR               INTEGER = 0;

    DECLARE @CURRENTLENGHT          INTEGER = 0;
    WHILE @CURRENTLENGHT < @DESIREDLENGTH
    BEGIN

        -- Do we want a number, special character or Alphabet Randonly decide?
        SET @SELECTOR  = CAST(ABS(CHECKSUM(NEWID())) % 3 AS INTEGER);   -- Always three 1 number , 2 alphaBET , 3 special;
        IF @SELECTOR = 0
        BEGIN
            SET @SELECTOR = 3
        END;

        -- SET SWAP VARIABLE AS DESIRED
        SELECT @SWAP = CASE WHEN @SELECTOR = 1 THEN @NUMBERS WHEN @SELECTOR = 2 THEN @ALPHABET ELSE @SPECIALS END;

        -- MAKE THE SELECTION
        SET @SELECTOR  = CAST(ABS(CHECKSUM(NEWID())) % LEN(@SWAP) AS INTEGER);
        IF @SELECTOR = 0
        BEGIN
            SET @SELECTOR = LEN(@SWAP)
        END;

        SET @RANDOMSTRING = ISNULL(@RANDOMSTRING,'') + SUBSTRING(@SWAP,@SELECTOR,1);
        SET @CURRENTLENGHT = LEN(@RANDOMSTRING);
    END;

END;

GO

DECLARE @RANDOMSTRING VARCHAR(8000)

EXEC GenerateARandomString @RANDOMSTRING = @RANDOMSTRING OUT

SELECT @RANDOMSTRING

Answer 18

There are a lot of good answers but so far none of them allow a customizable character pool and work as a default value for a column. I wanted to be able to do something like this:

alter table MY_TABLE add MY_COLUMN char(20) not null
  default dbo.GenerateToken(crypt_gen_random(20))

So I came up with this. Beware of the hard-coded number 32 if you modify it.

-- Converts a varbinary of length N into a varchar of length N.
-- Recommend passing in the result of CRYPT_GEN_RANDOM(N).
create function GenerateToken(@randomBytes varbinary(max))
returns varchar(max) as begin

-- Limit to 32 chars to get an even distribution (because 32 divides 256) with easy math.
declare @allowedChars char(32);
set @allowedChars = 'abcdefghijklmnopqrstuvwxyz012345';

declare @oneByte tinyint;
declare @oneChar char(1);
declare @index int;
declare @token varchar(max);

set @index = 0;
set @token = '';

while @index < datalength(@randomBytes)
begin
    -- Get next byte, use it to index into @allowedChars, and append to @token.
    -- Note: substring is 1-based.
    set @index = @index + 1;
    select @oneByte = convert(tinyint, substring(@randomBytes, @index, 1));
    select @oneChar = substring(@allowedChars, 1 + (@oneByte % 32), 1); -- 32 is the number of @allowedChars
    select @token = @token + @oneChar;
end

return @token;

end

Answer 19

I'm not expert in T-SQL, but the simpliest way I've already used it's like that:

select char((rand()*25 + 65))+char((rand()*25 + 65))

This generates two char (A-Z, in ascii 65-90).

Answer 20

This uses rand with a seed like one of the other answers, but it is not necessary to provide a seed on every call. Providing it on the first call is sufficient.

This is my modified code.

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND object_id = OBJECT_ID(N'usp_generateIdentifier'))
DROP PROCEDURE usp_generateIdentifier
GO

create procedure usp_generateIdentifier
    @minLen int = 1
    , @maxLen int = 256
    , @seed int output
    , @string varchar(8000) output
as
begin
    set nocount on;
    declare @length int;
    declare @alpha varchar(8000)
        , @digit varchar(8000)
        , @specials varchar(8000)
        , @first varchar(8000)

    select @alpha = 'qwertyuiopasdfghjklzxcvbnm'
        , @digit = '1234567890'
        , @specials = '_@#$&'
    select @first = @alpha + '_@';

    -- Establish our rand seed and store a new seed for next time
    set  @seed = (rand(@seed)*2147483647);

    select @length = @minLen + rand() * (@maxLen-@minLen);
    --print @length

    declare @dice int;
    select @dice = rand() * len(@first);
    select @string = substring(@first, @dice, 1);

    while 0 < @length 
    begin
        select @dice = rand() * 100;
        if (@dice < 10) -- 10% special chars
        begin
            select @dice = rand() * len(@specials)+1;
            select @string = @string + substring(@specials, @dice, 1);
        end
        else if (@dice < 10+10) -- 10% digits
        begin
            select @dice = rand() * len(@digit)+1;
            select @string = @string + substring(@digit, @dice, 1);
        end
        else -- rest 80% alpha
        begin
            select @dice = rand() * len(@alpha)+1;

            select @string = @string + substring(@alpha, @dice, 1);
        end

        select @length = @length - 1;   
    end
end
go

Answer 21

When generating random data, specially for test, it is very useful to make the data random, but reproducible. The secret is to use explicit seeds for the random function, so that when the test is run again with the same seed, it produces again exactly the same strings. Here is a simplified example of a function that generates object names in a reproducible manner:

alter procedure usp_generateIdentifier
    @minLen int = 1
    , @maxLen int = 256
    , @seed int output
    , @string varchar(8000) output
as
begin
    set nocount on;
    declare @length int;
    declare @alpha varchar(8000)
        , @digit varchar(8000)
        , @specials varchar(8000)
        , @first varchar(8000)
    declare @step bigint = rand(@seed) * 2147483647;

    select @alpha = 'qwertyuiopasdfghjklzxcvbnm'
        , @digit = '1234567890'
        , @specials = '_@# '
    select @first = @alpha + '_@';

    set  @seed = (rand((@seed+@step)%2147483647)*2147483647);

    select @length = @minLen + rand(@seed) * (@maxLen-@minLen)
        , @seed = (rand((@seed+@step)%2147483647)*2147483647);

    declare @dice int;
    select @dice = rand(@seed) * len(@first),
        @seed = (rand((@seed+@step)%2147483647)*2147483647);
    select @string = substring(@first, @dice, 1);

    while 0 < @length 
    begin
        select @dice = rand(@seed) * 100
            , @seed = (rand((@seed+@step)%2147483647)*2147483647);
        if (@dice < 10) -- 10% special chars
        begin
            select @dice = rand(@seed) * len(@specials)+1
                , @seed = (rand((@seed+@step)%2147483647)*2147483647);
            select @string = @string + substring(@specials, @dice, 1);
        end
        else if (@dice < 10+10) -- 10% digits
        begin
            select @dice = rand(@seed) * len(@digit)+1
                , @seed = (rand((@seed+@step)%2147483647)*2147483647);
            select @string = @string + substring(@digit, @dice, 1);
        end
        else -- rest 80% alpha
        begin
            declare @preseed int = @seed;
            select @dice = rand(@seed) * len(@alpha)+1
                , @seed = (rand((@seed+@step)%2147483647)*2147483647);

            select @string = @string + substring(@alpha, @dice, 1);
        end

        select @length = @length - 1;   
    end
end
go

When running the tests the caller generates a random seed it associates with the test run (saves it in the results table), then passed along the seed, similar to this:

declare @seed int;
declare @string varchar(256);

select @seed = 1234; -- saved start seed

exec usp_generateIdentifier 
    @seed = @seed output
    , @string = @string output;
print @string;  
exec usp_generateIdentifier 
    @seed = @seed output
    , @string = @string output;
print @string;  
exec usp_generateIdentifier 
    @seed = @seed output
    , @string = @string output;
print @string;  

Update 2016-02-17: See the comments bellow, the original procedure had an issue in the way it advanced the random seed. I updated the code, and also fixed the mentioned off-by-one issue.

Answer 22

I realize that this is an old question with many fine answers. However when I found this I also found a more recent article on TechNet by Saeid Hasani

T-SQL: How to Generate Random Passwords

While the solution focuses on passwords it applies to the general case. Saeid works through various considerations to arrive at a solution. It is very instructive.

A script containing all the code blocks form the article is separately available via the TechNet Gallery, but I would definitely start at the article.

Answer 23

select left(NEWID(),5)

This will return the 5 left most characters of the guid string

Example run
------------
11C89
9DB02

Answer 24

If you are running SQL Server 2008 or greater, you could use the new cryptographic function crypt_gen_random() and then use base64 encoding to make it a string. This will work for up to 8000 characters.

declare @BinaryData varbinary(max)
    , @CharacterData varchar(max)
    , @Length int = 2048

set @BinaryData=crypt_gen_random (@Length) 

set @CharacterData=cast('' as xml).value('xs:base64Binary(sql:variable("@BinaryData"))', 'varchar(max)')

print @CharacterData

Answer 25

I thought I'd share, or give back to the community ... It's ASCII based, and the solution is not perfect but it works quite well. Enjoy, Goran B.

/* 
-- predictable masking of ascii chars within a given decimal range
-- purpose: 
--    i needed an alternative to hashing alg. or uniqueidentifier functions
--    because i wanted to be able to revert to original char set if possible ("if", the operative word)
-- notes: wrap below in a scalar function if desired (i.e. recommended)
-- by goran biljetina (2014-02-25)
*/

declare 
@length int
,@position int
,@maskedString varchar(500)
,@inpString varchar(500)
,@offsetAsciiUp1 smallint
,@offsetAsciiDown1 smallint
,@ipOffset smallint
,@asciiHiBound smallint
,@asciiLoBound smallint


set @ipOffset=null
set @offsetAsciiUp1=1
set @offsetAsciiDown1=-1
set @asciiHiBound=126 --> up to and NOT including
set @asciiLoBound=31 --> up from and NOT including

SET @inpString = '{"config":"some string value", "boolAttr": true}'
SET @length = LEN(@inpString)

SET @position = 1
SET @maskedString = ''

--> MASK:
---------
WHILE (@position < @length+1) BEGIN
    SELECT @maskedString = @maskedString + 
    ISNULL(
        CASE 
        WHEN ASCII(SUBSTRING(@inpString,@position,1))>@asciiLoBound AND ASCII(SUBSTRING(@inpString,@position,1))<@asciiHiBound
         THEN
            CHAR(ASCII(SUBSTRING(@inpString,@position,1))+
            (case when @ipOffset is null then
            case when ASCII(SUBSTRING(@inpString,@position,1))%2=0 then @offsetAsciiUp1 else @offsetAsciiDown1 end
            else @ipOffset end))
        WHEN ASCII(SUBSTRING(@inpString,@position,1))<=@asciiLoBound
         THEN '('+CONVERT(varchar,ASCII(SUBSTRING(@Inpstring,@position,1))+1000)+')' --> wrap for decode
        WHEN ASCII(SUBSTRING(@inpString,@position,1))>=@asciiHiBound
         THEN '('+CONVERT(varchar,ASCII(SUBSTRING(@inpString,@position,1))+1000)+')' --> wrap for decode
        END
        ,'')
    SELECT @position = @position + 1
END

select @MaskedString


SET @inpString = @maskedString
SET @length = LEN(@inpString)

SET @position = 1
SET @maskedString = ''

--> UNMASK (Limited to within ascii lo-hi bound):
-------------------------------------------------
WHILE (@position < @length+1) BEGIN
    SELECT @maskedString = @maskedString + 
    ISNULL(
        CASE 
        WHEN ASCII(SUBSTRING(@inpString,@position,1))>@asciiLoBound AND ASCII(SUBSTRING(@inpString,@position,1))<@asciiHiBound
         THEN
            CHAR(ASCII(SUBSTRING(@inpString,@position,1))+
            (case when @ipOffset is null then
            case when ASCII(SUBSTRING(@inpString,@position,1))%2=1 then @offsetAsciiDown1 else @offsetAsciiUp1 end
            else @ipOffset*(-1) end))
        ELSE ''
        END
        ,'')
    SELECT @position = @position + 1
END

select @maskedString

Answer 26

This worked for me: I needed to generate just three random alphanumeric characters for an ID, but it could work for any length up to 15 or so.

declare @DesiredLength as int = 3;
select substring(replace(newID(),'-',''),cast(RAND()*(31-@DesiredLength) as int),@DesiredLength);

Answer 27

Heres something based on New Id.

with list as 
(
    select 1 as id,newid() as val
         union all
    select id + 1,NEWID()
    from    list   
    where   id + 1 < 10
) 
select ID,val from list
option (maxrecursion 0)

Answer 28

Here is a random alpha numeric generator

print left(replace(newid(),'-',''),@length) //--@length is the length of random Num.

Answer 29

Use the following code to return a short string:

SELECT SUBSTRING(CONVERT(varchar(40), NEWID()),0,9)

Answer 30

I did this in SQL 2000 by creating a table that had characters I wanted to use, creating a view that selects characters from that table ordering by newid(), and then selecting the top 1 character from that view.

CREATE VIEW dbo.vwCodeCharRandom
AS
SELECT TOP 100 PERCENT 
    CodeChar
FROM dbo.tblCharacter
ORDER BY 
    NEWID()

...

SELECT TOP 1 CodeChar FROM dbo.vwCodeCharRandom

Then you can simply pull characters from the view and concatenate them as needed.

EDIT: Inspired by Stephan's response...

select top 1 RandomChar from tblRandomCharacters order by newid()

No need for a view (in fact I'm not sure why I did that - the code's from several years back). You can still specify the characters you want to use in the table.