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

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

3

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.

4

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

7

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.