Add new comment

SQL: generate random character string

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 owing to the 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