[SOLVED] SQL Server : generate random numeric string 27 characters long

SQL Server : generate random numeric string 27 characters long

I need to insert random data in my database for testing, and would need to generate a numeric string (can start with 0) 27 characters long.

I've been looking into NEWID() but it contains also letters, same for NEWSEQUENTIALID().

So far my approach would be to make a while loop, generate each digit randomly and concatenate it, but it seems to be a very slow approach.

I am using MSSQL 2014.

Solution

A possible solution, based on this approach (using CHECKSUM() and NEWID()):

CREATE TABLE TestTable(RandomNumber varchar(27))
DECLARE @length int = 27

;WITH rCTE AS (
   SELECT 1 AS n 
   UNION ALL 
   SELECT n + 1 
   FROM rCTE 
   WHERE n < @length
)
INSERT INTO TestTable(RandomNumber)
VALUES ((SELECT ABS(CHECKSUM(NEWID())) % 10 FROM rCTE FOR XML PATH('')))
-- For SQL Server 2017+
-- VALUES ((SELECT STRING_AGG(ABS(CHECKSUM(NEWID())) % 10, '') FROM rCTE))