Generating random 6 digit numbers

Question

Post reply

Generating random 6 digit numbers

shailesh-519005

SSChasing Mays

Points: 643
More actions
December 25, 2008 at 9:35 pm

#197503

Merry Christmas to All,
I need to generate 6 digit random numbers( exectly 6 digit) through sql query. How can i do it....
I tried
select cast((rand()*1000000) as decimal(6))
But some times it is giving 5 digit numbers also.I need execly 6 digits
Please Help..
Thanks
Shailesh

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Paresh Prajapati SSCertifiable Points: 7567 More actions · Answer 1

You can do onne thing

Assigned fisrt generated number to first varible.

Assigned second generated number to second varible.

Then using substring u can concating the two variable by taking 3 of 1 variable and 3 of other varible.

_____________________________________________________________________________________________________________
Paresh Prajapati
➡ +919924626601
http://paresh-sqldba.blogspot.com/[/url]
LinkedIn | Tweet Me | FaceBook | Brijj

Yousaf Khan Ten Centuries Points: 1149 More actions · Answer 2

Dear Shailesh!

Follow the Bellow code it will give you exactlly 6 no

declare @no int

set @no = (select cast((rand()*1000000) as decimal(6)))

if len(@no) = 6

select @no

else

set @no = convert(varchar,(select cast((rand()*10) as decimal(1))))+convert(varchar,@no)

Regards,

Yousaf Khan (Pakistan)

Littlemaster.yousaf@gmail.com

ramu.valleti SSCrazy Points: 2229 More actions · Answer 3

Hi thanks for the reply..

When i execute this code continuously 5 to 10 times, in between it is showing a message saying..

'Command(s) completed successfully.'

can you tell what would be the reason?

Ramu
No Dream Is Too Big....!

shailesh-519005 SSChasing Mays Points: 643 More actions · Answer 4

Thank u guys for the reply. But i came up with this solution...

select cast((900000* Rand() + 100000) as int )

This is also working according to my requirement..

Thanks

Shailesh:)

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 5

Also refer

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/10/10/generating-random-numbers-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

Yousaf Khan Ten Centuries Points: 1149 More actions · Answer 6

Sorry i was away from last 2 day's

actually there a little change in code

the correct code is

declare @no int

set @no = (select cast((rand()*1000000) as decimal(6)))

if len(@no) = 6

select @no

else

select @no = convert(varchar,(select cast((rand()*10) as decimal(1))))+convert(varchar,@no)

The Only change is to write "select" insted of "Set" in else section

false

set @no = convert(varchar,(select cast((rand()*10) as decimal(1))))+convert(varchar,@no)

true

select @no = convert(varchar,(select cast((rand()*10) as decimal(1))))+convert(varchar,@no)

Regards,

Yousaf Khan (pakistan)

littlemaster.yousaf@gmail.come

+923335797097

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 7

Here's an old note I found in my archives.

Hope it may be of some help.

The general rule to use rand to generate random numbers from {lower limit} to {upper limit} is:

select convert(int, {upper limit - lower limit + 1} * rand() + {lower limit} )

So, to get numbers from 100000 to 999999: (all numbers in the 6-digit range)

select convert(int, 900000 * rand() + 100000)

=;o)

/Kenneth

Yousaf Khan Ten Centuries Points: 1149 More actions · Answer 8

Dear Kenneth

These Both has the same functionalty

that shailesh got and you mentioned

from you

select convert(int, 900000 * rand() + 100000)

from shailesh

select cast((900000* Rand() + 100000) as int )

Note Cast and convert are providing the same function.:

Regards,

Yousaf khan:P

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 9

Ah, you're right.

Didn't read the thread carefully enough.

btw, the convert/cast difference is only because I copied mine from an old note made back when cast wasn't yet available in SQL Server..

Anyway, sry about the dupe post.

/Kenneth

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 10

I wouldn't use RAND() to generate the random number because if more than one row is returned in a given Select that uses it, ALL of the supposedly random numbers will be the same.

Take a look at the link Madhivanan posted... it's one of the right ways to do it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 11

For the record, here's how I'd do it... very very fast because there's only 1 conversion and the rest is all integer math...

SELECT ABS(CHECKSUM(NEWID()))%900000 + 100000

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Yousaf Khan Ten Centuries Points: 1149 More actions · Answer 12

Dear Kenneth

Your Post is very usefull for the new t-sql users

who do not know about cast/convert. there is no matter

of sry every kind of information is usefull post every

thing that you know as every body need it share

your knowledge.

Best Regards,

Yousaf Khan:)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 13

Here is one more method to generate a random number in the range of 100000 to 999999.

It uses the right 7 bytes of the newid() to create a bigint value. Because negatives are defined by the left-most bit of the 8 byte bigint, the values can only be positive and there is no need for the abs function call.

select convert(bigint,convert(varbinary(7),newid()))%900000+100000

I will leave it to Jeff to test which method is the fastest and produces the most random distribution. 🙂

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 14

The cast to VarBinary takes more time, Michael. That's the way I used to do it before Peter Larson and Matt Miller showed me this other way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)