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
