I need to create a string of random digits in TSQL
I thought about HASH + CONVERT
But convert works on style - so I am not show how can I do it if data type of result and expression are both char(100) for example
Any advices?
Add a comment
|
4 Answers
SET the @Length to what you need. Add more copies of sys.objects as necessary.
DECLARE @Length INT
SET @Length = 10000
DECLARE @RandomDigits VARCHAR(MAX)
SET @RandomDigits = ''
SELECT TOP (@Length) @RandomDigits = @RandomDigits + RIGHT(CHECKSUM(NEWID()), 1)
FROM sys.objects a, sys.objects b, sys.objects c
SELECT @RandomDigits
Comments
To get a string of 100 random digits, concatenate output of CHECKSUM over NEWID function:
select substring(list, 1, 100)
from (
select c as [text()]
from (
select cast(abs(checksum(newid())) as varchar(max)) as c
from sys.objects
) x
for xml path('')
) x(list)
Comments
One way is to use a recursive CTE:
with cte as (
select cast(right(checksum(newid()), 1) as varchar(8000)) as val, 1 as len
union all
select val + right(checksum(newid()), 1), len + 1
from cte
where len < 100
)
select *
from cte
where len = 100;
Comments
if the number of digits is less or equals 10, you can just copy below code
DECLARE @digitsCount INT = 5;
SELECT RIGHT(CHECKSUM(NEWID()), @digitsCount)
