Create random string of digits T-SQL

Question

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?

Answer 1

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

Answer 2

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)

Answer 3

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;

Answer 4

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)