CODEWITHSUNDEEP
sqlsql-server
user2475604
user2475604

Reputation:

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

Upvotes: 2

Views: 6879

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44326

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

Upvotes: 5

codingbadger
codingbadger

Reputation: 43984

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.

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

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

Upvotes: -1

Related Questions