SQL Server : generate random numeric string 27 characters long

Question

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.

@Zhorov, no because it generates a single small integer that is up to 2 characters long. not 27. — sharkyenergy
– sharkyenergy, Commented Nov 19, 2021 at 6:59
select it from a TALLY table with the required number of digits and STRING_AGG() it — Squirrel
– Squirrel, Commented Nov 19, 2021 at 7:03
I'll offer this, but not as an answer, since then I'd be required to explain and defend it -- select right(replicate('0', 27) + convert(decimal(38, 0), 0x26000001 + crypt_gen_random(13)), 27). This approach cannot generate arbitrarily long values, but it's likely to be faster than anything else (where such things matter). — Jeroen Mostert
– Jeroen Mostert, Commented Nov 19, 2021 at 8:05

Zhorov · Accepted Answer · 2021-11-19 07:39:13Z

3

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))

edited Nov 19, 2021 at 7:39

answered Nov 19, 2021 at 7:04

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sharkyenergy Over a year ago

Thank you, unfortunately I am on MsSql 2014 and that does not have the STRING_AGG function. I add this information to the post

sharkyenergy Over a year ago

thank you, but how can I add this result to a insert statement? I tried but got the message that FOR XML is not allowed in an insert statement.

Ori N · Accepted Answer · 2024-09-11 10:07:07Z

0

I'm using presto and and I found this solution:

SELECT LPAD(CAST(RANDOM(1000000000) AS VARCHAR), 27, '0')

answered Sep 11, 2024 at 10:07

Ori N

8282 gold badges16 silver badges27 bronze badges

Collectives™ on Stack Overflow

SQL Server : generate random numeric string 27 characters long

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related