seven digit random number

Question

Post reply

seven digit random number

samita1123-956737

SSC Enthusiast

Points: 184
More actions
June 5, 2014 at 12:41 pm

#308773

How to generate a seven digit number randomly in sql server 2008

Viewing 10 posts - 1 through 9 (of 9 total)

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

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 1

SQL Guy 1

SSCoach

Points: 15979

June 5, 2014 at 12:48 pm

#1719410

SELECT FLOOR(RAND() * 10000000)

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

SQL Guy 1 (6/5/2014)
SELECT FLOOR(RAND() * 10000000)

That will also return 6 digit values.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

samita1123-956737 (6/5/2014)
How to generate a seven digit number randomly in sql server 2008

This is a bit vague. We can show you how to generate a 7 digit number but I have a feeling you want more than that. Do these need to also be unique values?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

samita1123-956737 SSC Enthusiast Points: 184 More actions · Answer 4

samita1123-956737

SSC Enthusiast

Points: 184

June 5, 2014 at 12:55 pm

#1719420

Yes they should be unique values

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 5

All at once or one at a time?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

If you can tell us the entire story of what you want we don't have to keep coming back with more questions. So far we know you want a 7 digit random that must be unique. Do you have a table this belong in or this a separate table of these values? Do you need to generate these one at a time (like for an insert) or do you need a big list of them? The reason is because depending on the needs it will greatly affect how to go about this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 7

Sean Lange (6/5/2014)
SQL Guy 1 (6/5/2014)
SELECT FLOOR(RAND() * 10000000)
That will also return 6 digit values.

SELECT POWER (10,6) + FLOOR(RAND()*POWER(10,6.9))

Alan Burstein SSC Guru Points: 61141 More actions · Answer 8

You could go with:

SELECT cast(8999999*rand() as int)+1000000

For multiple rows you could go with:

SELECT cast(8999999*rand(cast(newid() AS varbinary)) as int)+1000000

FROM <something>

Edit: typo

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Xedni SSCertifiable Points: 6951 More actions · Answer 9

I'd be interested in hearing what the use case is for this as there's probably a much more tailored answer to your question.

There are exactly 8,999,999 distinct integers between 1,000,000 and 9,999,999. Depending on what these numbers are being generated for, you could start having collisions reasonably soon (thanks to our good friend the birthday paradox). If the only criteria is length and uniqueness, and identity column starting at 1000000 with a check constraint limiting it to less than 10,000,000 would constraint it such.

Executive Junior Cowboy Developer, Esq.[/url]