Generate a random 9 digit number

I am trying to generate a random 9 digit number

for example 0987654321 note that each digit appear once..

i tried this :

select convert(numeric(10,0),rand() * 8999999999) + 10000000

it generates but at some point numbers repeat. but it is client need to make sure in 9 digit sequence no number appears twice..

required output like this..

1234567890
9870654321
1234098567
8976543120

3 Answers

It seems you are trying to generate a 10 digit number. I made it as a varchar because I don't want to supress the first 0 when applies:

declare @rnd varchar(10) = ''
;with a as
(
select 0 x
union all
select x + 1
from a where x < 9
), b as
(
select top 10 x from a
order by newid()
)
select @rnd += cast(x as char(1)) from b
select @rnd

You can also write it is a while loop:

DECLARE @rnd varchar(10) = '0123456789'
DECLARE @i int = len(@rnd)
;while @i > 1 select @rnd = stuff(@rnd, rnd, 1, '') + substring(@rnd, rnd, 1), @i += -1 from (SELECT cast(rand(BINARY_CHECKSUM(newid()))*@i as int)+1 rnd) x
select @rnd

As you want to insert these values in to a table, and also check for duplicated. I would do the following:

1. Create a view to generate a NewId

create view vw_getNewID as select newid() AS [NewId]

2. Generate a TVF

CREATE FUNCTION dbo.fn_GenerateRandomNumber()
RETURNS @r TABLE
(
RandomNumber varchar(10)
)
AS BEGIN ;WITH numbers as ( SELECT 0 AS [number] UNION ALL SELECT number + 1 FROM numbers WHERE number < 9 ) INSERT INTO @r SELECT stuff( (SELECT ''+ number FROM numbers CROSS APPLY dbo.vw_getNewID vgni order by vgni.NewId FOR XML PATH(''),TYPE ).value('.','VARCHAR(MAX)') , 1, 1, '') RETURN
END

3. Query your new TVF to insert into your table.

INSERT INTO dbo.YourTable (RandomNumber)
SELECT grn.RandomNumber
FROM dbo.fn_GenerateRandomNumber() grn
LEFT JOIN dbo.YourTable t ON t.RandomNumber = grn.RandomNumber
WHERE t.RandomNumber IS NULL

** Note that I have converted the output to varchar to preserve the leading zero.

Select top 10 *
from
(
select 100000000 + CONVERT(BIGINT, (9999999999-100000000+1)*RAND()) as rnd
union
select 100000000 + CONVERT(BIGINT, (9999999999-100000000+1)*RAND()) as rnd
union
select 100000000 + CONVERT(BIGINT, (9999999999-100000000+1)*RAND()) as rnd
union
select 100000000 + CONVERT(BIGINT, (9999999999-100000000+1)*RAND()) as rnd
union
select 100000000 + CONVERT(BIGINT, (9999999999-100000000+1)*RAND()) as rnd
union
select 100000000 + CONVERT(BIGINT, (9999999999-100000000+1)*RAND()) as rnd
union
select 100000000 + CONVERT(BIGINT, (9999999999-100000000+1)*RAND()) as rnd
) as t