Update HELP

Question

Update HELP

Viewing 9 posts - 16 through 23 (of 23 total)

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

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 1

Ok, Chad... totally fake it is...

Just so folks know... an area code and exchange combination is referred to as an NPA/NXX. The letters are not random in nature...

"N" means "any digit 2 thru 9"

"P" means "any digit 0 thru 8"

"A" means "any digit 0 thru 9"

"X" means "any digit 0 thru 9 with certain "conditions". For example, one condition is that XX may not be "11" because it can be confused with things like "411" or "911". One of the "X"s may be a "1" so long as the other is not.

The reason why I asked if they had to be "real" phone numbers or not is because a lot of systems test against either a Telcordia, CCMI, or NANPA provided database to make sure the NPA/NXX is actually active and the rest of the phone number (the last four digits) has been activated either as a "10 thousand block" or (more recently) a "thousand block". If your system did such a check and well as validating for things like the "XX" rule, it would be a lot more work but I thought I'd make the offer.

Since we're going with fake numbers, we're going to force them to be trully fake... we'll make the first digit of both the NPA and NXX = 1.

Since RAND() always returns the same random number across all rows of a single query, John's good effort won't do it. Sure, RAND() will change if you run the query again but, like a single instance of GETDATE(), it will be the same for all rows returned in a single query. Ironically, RAND() needs a random seed to return different random numbers for each row in a single query. There's only one thing that will return something random in SQL Server. NEWID().

Note the NEWID() can't be used by RAND() directly. It has to be converted to something like VARBINARY (or whatever) and such explicit conversions (especially to VARBINARY) are expensive. With that in mind, a lot of us have totally given up on most uses of RAND() in favor of using CHECKSUM to convert NEWID() to an unconstrained random number and then use "modulus" to constrain the range of random numbers and simple addition to control the starting value.

That's all geekinese for creating as many fake phone numbers as you want... I don't know the name of your table so please remember to make the appropriate substitution. (I'll let you "struggle" with why we need the ABS function here, it will be obvious, though)

UPDATE yourtable

SET PhoneNumber = '(' + LEFT(ABS(CHECKSUM(NEWID())) % 1000 + 1000,3) + ') '

+ LEFT(ABS(CHECKSUM(NEWID())) % 1000 + 1000,3) + '-'

+ LEFT(ABS(CHECKSUM(NEWID())) % 10000 + 10000,4)

If someone just wants to generate a million bogus numbers (with some dupes as random numbers allow), you can run this code...

SELECT TOP 1000000

PhoneNumber = '(' + LEFT(ABS(CHECKSUM(NEWID())) % 1000 + 1000,3) + ') '

+ LEFT(ABS(CHECKSUM(NEWID())) % 1000 + 1000,3) + '-'

+ LEFT(ABS(CHECKSUM(NEWID())) % 10000 + 10000,4)

FROM Master.sys.All_Columns ac1

CROSS JOIN Master.sys.All_Columns ac2

I have to say it again, though... there are two things dreadfully wrong with this whole notion.

1. You should never ever store formatted phone numbers. Leave the formatting off because the formatting requirements could (and will) change at the drop of a hat.

2. You should never ever store a whole phone number in a single column. Always break the NPA/NXX/XXXX parts apart. There're a million reasons for doing this just like you should never ever store a human's full name in a single column. The biggest reason is that it violates normalization and every time you do that, it will come back to haunt you sometime in the future... the exceptions are just folks that haven't run into the problems, yet.

Now, you not only have the rocket ship you were looking for but, with a little effort on your part, you could probably build another one to do something else.

--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)

Garadin One Orange Chip Points: 29613 More actions · Answer 2

Jeff Moden (11/24/2009)
The biggest reason is that it violates normalization and every time you do that, it will come back to haunt you sometime in the future...

This strikes me as a statement that could launch a very, very long thread all on its own. <-- (That didn't help.) 😉

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Steve Jones - SSC Editor SSC Guru Points: 734489 More actions · Answer 3

Jeff,

Nice post, and a great way to handle the phone numbers. I wasn't aware of NPA/NXX, and that's good to know.

I would agree with you that storing these values in one column is a bad idea. However if you're stuck with it, work with it as best you can.

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 4

Garadin (11/24/2009)
Jeff Moden (11/24/2009)
The biggest reason is that it violates normalization and every time you do that, it will come back to haunt you sometime in the future...
This strikes me as a statement that could launch a very, very long thread all on its own. <-- (That didn't help.) 😉

Heh... true enough. Might be a good thing for someone to write an article on... like you, I believe it would spawn a nearly unending discussion of pro's and con's on the subject.

You want it, Seth? I'd be happy to help out with a pre-review.

--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: 1003871 More actions · Answer 5

Steve Jones - Editor (11/24/2009)
Jeff,
Nice post, and a great way to handle the phone numbers. I wasn't aware of NPA/NXX, and that's good to know.
I would agree with you that storing these values in one column is a bad idea. However if you're stuck with it, work with it as best you can.

Thansk for the feedback and, yes, I reluctantly agree... that's why I produced the single column version. Chances are the folks the OP has to deal with are probably pretty insistent on the single column formatted stuff.

--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)

cm62597 SSC Eights! Points: 976 More actions · Answer 6

cm62597

SSC Eights!

Points: 976

November 25, 2009 at 8:16 am

#1084075

Thanks for you help....I have learned a lot.

Chad

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 7

Thanks, Chad... I appreciate the feedback.

--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)

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 8

Did you actually try that, John? It returns exactly the same phone number for all rows.

--Jeff Moden

-------------------------------------------------------------------------------------

No i did not and that is why i am an idiot. Was doing several things at the same time....sry

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 9

john scott miller (11/25/2009)
Did you actually try that, John? It returns exactly the same phone number for all rows.
--Jeff Moden
-------------------------------------------------------------------------------------
No i did not and that is why i am an idiot. Was doing several things at the same time....sry

Heh.. that's funny. You fit in with the rest of us just fine. We've all had the same thing happen. Thanks for both your honesty and the feedback. Welcome aboard, too! 🙂

--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)