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 
