Generating random strings with T-SQL

sql

prompt-engineering

best-practices

performance

byAnton Shumikhin·Sep 13, 2024

To generate a 10-character random string in T-SQL, use:

SELECT SUBSTRING(CONVERT(varchar(36), NEWID()), 1, 10) AS RandomString

This uses NEWID() to generate a unique identifier, CONVERT to transform it to a varchar type, and SUBSTRING to extract the desired number of characters.

To exclude dashes, which are part of the GUID, use REPLACE:

SELECT REPLACE(SUBSTRING(CONVERT(varchar(36), NEWID()), 1, 13), '-', '') AS RandomString

For high-volume usage, use crypt_gen_random() instead:

SELECT SUBSTRING(CONVERT(varchar(36), CRYPT_GEN_RANDOM(10)), 1, 10) AS RandomString

Deeper dive into T-SQL

Implementing custom characters

For specific character sets, create a T-SQL function for control:

CREATE FUNCTION dbo.GenerateRandomString(@Length int)
RETURNS NVARCHAR(100)
AS
BEGIN
    -- "I have exactly 62 reasons to love SQL." - Random SQL Dev
    DECLARE @characters NVARCHAR(62) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', 
            @output NVARCHAR(100) = ''
    WHILE LEN(@output) < @Length
    BEGIN
        SELECT @output = @output + SUBSTRING(@characters, CONVERT(int, rand() * LEN(@characters)) + 1, 1)
    END
    RETURN @output
END

Then call the function:

SELECT dbo.GenerateRandomString(10) AS RandomString

Addressing unwanted characters

If you need to exclude certain characters when generating your string, CHARINDEX can be applied recursively:

-- "It's like getting extra cilantro when you said 'no cilantro'." - Dev who hates '-'
WHILE CHARINDEX('-', @RandomString) > 0
BEGIN
    SET @RandomString = REPLACE(@RandomString, '-', '')
END

Increasing randomness factor

For added randomness, or when you want to make your DBA scratch their head, advance the random seed:

-- "Because GUIDs are just too mainstream." - Hipster Dev
SELECT SUBSTRING(CONVERT(varchar(36), NEWID()), 1, 10) + 
       SUBSTRING(CONVERT(varchar(36), HASHBYTES('md5', CONVERT(varbinary, GETDATE()))), 1, 10)

Optimizing large-scale generation

For generating large volumes of strings, optimize for performance. Here's how you could do it:

-- "Because doing things faster matters. Ask any Hare." - Tortoise SQL developer
INSERT INTO YourTable (RandomString)
SELECT TOP (1000000) 
       REPLACE(SUBSTRING(CONVERT(varchar(36), NEWID()), 1, 13), '-', '') AS RandomString
FROM SomeLargeTable;

Ensuring reproducibility

Need the same batch of random strings again? Employ a seeded RAND method for reproducibility:

-- 'As predictable as my morning coffee.' - Dev who likes routine
SELECT CHAR(ROUND((RAND(12345) * 25) + 65, 0))

Crafting perfect strings

Adjusting string length

Different scenarios, different length requirements. Adjust the SUBSTRING method:

-- 'There's no such thing as one size fits all.' - Wise SQL Sage
DECLARE @DesiredLength INT = 15;
SELECT SUBSTRING(CONVERT(varchar(36), NEWID()), 1, @DesiredLength) AS RandomString

Evading common pitfalls

Off-by-one errors could ruin your party. Always double-check:

-- 'Cause nobody likes an uninvited guest.' - DBA who dislikes errors
SELECT LEN(RandomString), RandomString 
FROM (SELECT SUBSTRING(CONVERT(varchar(36), NEWID()), 1, 10) AS RandomString) AS DerivedTable