Need simple way (using t-sql) to generate "realistic"-looking numbers...

Question

Post reply

Need simple way (using t-sql) to generate "realistic"-looking numbers...

Barkingdog

SSCoach

Points: 18670
More actions
March 24, 2010 at 4:21 pm

#220319

I'm writing a script where I want to insert bogus SSN data that looks like this:
"235978574"
or
"784098723'
What's a simple way of generating such data? (I was playing around with "select (left (newid(),9)"
but it generates aphanumeric numbers containing "-" characters. I don't want "-" or the alphabet appearing in the 9 digit string)
TIA,
Barkingdog

Viewing 12 posts - 1 through 11 (of 11 total)

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

Eric Zierdt Ten Centuries Points: 1333 More actions · Answer 1

SELECT CAST((RAND()*1000000000) AS INT)

Let me know if that works for ya

Lowell SSC Guru Points: 323482 More actions · Answer 2

i have this saved in my snippets; change the top 1000 if you need more; this version includes the dashes in the SSN

--results

RAND_STRING

675-45-3155

323-95-9117

024-41-4469

939-91-4719

966-36-0213

the code:

--neat , creates a random string that avoids issues where values look to similar, like B=8,O=0, 2=Z,1=I; allwed chars are in the X string for cleaner trnascription

select top 1000

RAND_STRING from

(select

RAND_STRING =

substring(x,(abs(convert(bigint,convert(varbinary(100),newid())))%len(x))+1,1)+

substring(x,(abs(convert(bigint,convert(varbinary(100),newid())))%len(x))+1,1)+'-'+

substring(x,(abs(convert(bigint,convert(varbinary(100),newid())))%len(x))+1,1)+

substring(x,(abs(convert(bigint,convert(varbinary(100),newid())))%len(x))+1,1)+ '-'+

substring(x,(abs(convert(bigint,convert(varbinary(100),newid())))%len(x))+1,1)+

substring(x,(abs(convert(bigint,convert(varbinary(100),newid())))%len(x))+1,1)

from

( select top 1000 x = '345679210' from syscolumns ) aa ) a

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Quatrei.X SSCrazy Points: 2697 More actions · Answer 3

I agree with Eric

SELECT CAST((RAND()*1000000000) AS INT)

since you said no "-"

but if it includes dashes like lowell's example, thi is my code

DECLARE @i INT

SET @i=1

DECLARE @tbl TABLE (SSN NVARCHAR(11))

WHILE @i<=1000

BEGIN

INSERT INTO @tbl

SELECTRIGHT('000'+CAST(CAST((RAND()*1000) AS INT) AS NVARCHAR(MAX)),3) + '-'

+RIGHT('00'+CAST(CAST((RAND()*100) AS INT) AS NVARCHAR(MAX)),2) + '-'

+RIGHT('0000'+CAST(CAST((RAND()*10000) AS INT) AS NVARCHAR(MAX)),4)

SET @i = @i + 1

END

SELECT SSN FROM @tbl

just trying things out... ^__^

_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

SwePeso SSC-Dedicated Points: 39725 More actions · Answer 4

Short and simple

SELECTABS(CHECKSUM(NEWID())) % 1000000000

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Eric Zierdt (3/24/2010)
SELECT CAST((RAND()*1000000000) AS INT)
Let me know if that works for ya

That only works for one row at a time which means you'll also need to use some form of RBAR to generate more than one row at a time. Take a look at Peso's code above and start from there. Don't forget those leading zero's though. 😉

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Quatrei.X (3/25/2010)
I agree with Eric
SELECT CAST((RAND()*1000000000) AS INT)
since you said no "-"
but if it includes dashes like lowell's example, thi is my code
DECLARE @i INT
SET @i=1
DECLARE @tbl TABLE (SSN NVARCHAR(11))
WHILE @i<=1000
BEGIN
INSERT INTO @tbl
SELECTRIGHT('000'+CAST(CAST((RAND()*1000) AS INT) AS NVARCHAR(MAX)),3) + '-'
+RIGHT('00'+CAST(CAST((RAND()*100) AS INT) AS NVARCHAR(MAX)),2) + '-'
+RIGHT('0000'+CAST(CAST((RAND()*10000) AS INT) AS NVARCHAR(MAX)),4)
SET @i = @i + 1
END
SELECT SSN FROM @tbl
just trying things out... ^__^

As your signature line says, try this out... first, change the number in your good code to 1 million and run it. Then try the following...

SELECT TOP 1000000

STUFF(

RIGHT('000000000'+CONVERT(VARCHAR(9),ABS(CHECKSUM(NEWID()))%1000000000),9)

,6,0,'-')

,4,0,'-')

FROM Master.sys.All_Columns ac1

CROSS JOIN Master.sys.All_Columns ac2

... avoid RBAR. 😛

--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)
Intro to Tally Tables and Functions

SwePeso SSC-Dedicated Points: 39725 More actions · Answer 7

The good Lord allows you to jump in, huh Jeff? 😀

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

SwePeso (3/27/2010)
The good Lord allows you to jump in, huh Jeff? 😀

Ummm... not sure I understand the reference or the question, Peter. What do you mean or have I just not had enough coffee? 🙂

--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)
Intro to Tally Tables and Functions

Barkingdog SSCoach Points: 18670 More actions · Answer 9

Thanks to everyone for the good ideas. All of the posts offered different perspectives, just what I wanted.

Barkingdog

vidya_pande SSCertifiable Points: 5275 More actions · Answer 10

hi Barkingdog,

just curiosity- r u generting these number for test data or some kind of data masking.

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

Jeff Moden (3/27/2010)
SwePeso (3/27/2010)
The good Lord allows you to jump in, huh Jeff? 😀
Ummm... not sure I understand the reference or the question, Peter. What do you mean or have I just not had enough coffee? 🙂

Heh... never mind... I get it. Busy, busy, busy... 😛 Gotta take a break for a "post" here and there just to break the monotony. :hehe:

Check your email, Peter... I finally cleaned up the "contest". 🙂

--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)
Intro to Tally Tables and Functions