Generating random 6 digit numbers
-
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
-
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.
-
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)
-
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....! -
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:)
-
Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/10/10/generating-random-numbers-part-ii.aspx
MadhivananFailing to plan is Planning to fail
-
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
-
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
-
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
-
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
-
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) -
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) -
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:)
-
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+100000I will leave it to Jeff to test which method is the fastest and produces the most random distribution.
-
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)
+919924626601Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply