Collection of methods to generate random string in a single SQL statement

M1: Recursive string concatenation

On SQL Server the method is limited by 100 characters because of CTE recursion level limitation. The method has the performance issues when using in loops/joins because the table always have 100 rows generated every time.

DECLARE @some_length int = 99;
WITH random_char(c) AS (
   SELECT char(convert(int, rand(abs(convert(int, convert(varbinary, newid())))) * 60.0) + 32)
),
t1 (char_count, s) AS (
   SELECT 0, convert(nvarchar(MAX), (SELECT c FROM random_char))
   UNION ALL
   SELECT char_count + 1, s + (SELECT c FROM random_char) AS s
   FROM t1 WHERE char_count < 100
)
SELECT * FROM t1 WHERE char_count = @some_length

M2: Concatenate rows

The method is used SQL Server feature to concatenate row values into one string. Since 2017 version you'd better use STRING_AGG() function instead.

There are no string length limitation nor performance issues.

DECLARE @max_length int = 150;
DECLARE @s nvarchar(max) = '';
WITH chars(n, c) AS (
   SELECT 1, 'A' UNION ALL
   SELECT 2, 'b' UNION ALL
   SELECT 3, 'C' UNION ALL
   SELECT 4, 'd' UNION ALL
   SELECT 5, 'E' UNION ALL
   SELECT 6, 'f' UNION ALL
   SELECT 7, 'G'
   -- add more characters here to include them in a random string
),
lines AS (
   SELECT 1 AS n UNION ALL
   SELECT 2 UNION ALL
   SELECT 3 UNION ALL
   SELECT 4 UNION ALL
   SELECT 5 UNION ALL
   SELECT 6 UNION ALL
   SELECT 7 UNION ALL
   SELECT 8 UNION ALL
   SELECT 9 UNION ALL
   SELECT 10
),
all_lines AS (
   SELECT ROW_NUMBER() OVER(ORDER BY l1.n) AS n,
          convert(int, rand(abs(convert(int, convert(varbinary, newid())))) * (SELECT COUNT(n) FROM chars)) + 1 AS char_n
   FROM lines l1
        CROSS JOIN lines l2 -- 100
        CROSS JOIN lines l3 -- 1000
        -- add more CROSS JOINs here to get longer string
),
only_lines AS (
   SELECT * FROM all_lines
   WHERE n <= @max_length
)
SELECT @s = @s + c
FROM only_lines l INNER JOIN chars c ON l.char_n = c.n
SELECT @s

Mechanics of software Programming is thinking, not typing
Home \| Categories \| Tags \| Search	\| eng \| рус