SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Random Phone numbers


Random Phone numbers

Author
Message
colemanj
colemanj
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 33
The table currently has a column with phone numbers, however I need to
randomize/scramble them up so that the "real live" phone numbers are not visible.

thanks,

JC
Michael Earl-395764
Michael Earl-395764
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21705 Visits: 23078
Is this for some kind of testing system, or is this for some kind of security purposes in a production database?
Jason Selburg
Jason Selburg
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10131 Visits: 4131
Something like this should mix up your numbers ... you may have to run it a couple of times to verify that you get all records.


WITH startingList
AS (SELECT
ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn
,PKfield -- this is the unigue identifier for the records, its PK if you have one
,phoneNumber
FROM yourTable) -- your table's name
,jumbledList
AS (SELECT
ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn
,phoneNumber AS newnumber
FROM yourTable) -- your table's name
UPDATE a
SET a.phoneNumber = c.newNumber
FROM
yourTable AS a
INNER JOIN startingList AS b
ON a.PKfield = b.PKfield
INNER JOIN jumbledList AS c
ON b.rn = c.rn
WHERE
a.phoneNumber <> c.newNumber



______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Matt Miller (4)
Matt Miller (4)
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46700 Visits: 19262
Try something like this...

select RIGHT('1234567890'+cast(cast(9999999999*rand(checksum(newid())) as bigint) as varchar(10)),10)



----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Jason Selburg
Jason Selburg
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10131 Visits: 4131
Matt Miller (4/17/2008)
Try something like this...

select RIGHT('1234567890'+cast(cast(9999999999*rand(checksum(newid())) as bigint) as varchar(10)),10)



OK, that just blows the pants off mine. Hehe

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
colemanj
colemanj
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 33
security purposes on a production database
Michael Earl-395764
Michael Earl-395764
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21705 Visits: 23078
So I assume you want some way to "unscramble" the phone numbers?
If that is the case, you probably want to look into encryption.
Michael Earl-395764
Michael Earl-395764
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21705 Visits: 23078
Oh - and Matt - that's pretty slick.

Hats off to you.
Matt Miller (4)
Matt Miller (4)
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46700 Visits: 19262
Michael Earl (4/17/2008)
Oh - and Matt - that's pretty slick.

Hats off to you.


thanks - it works pretty well as long as you dont' have to say it ten times fast....that's quite the mouthful!!!!

By the way - in case you have duplicate phone numbers in your dataset...if you replace NEWID() with your existing phone number, this should generate the same random number for every duplicated instance....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
GSquared
GSquared
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94659 Visits: 9730
colemanj (4/17/2008)
security purposes on a production database


In that case DO NOT REPLACE THEM!!!!!

If you replace them with random numbers, they'll be gone.

Instead, use encryption. That way, you can get them back for authorized, appropriate use, but can hide them from unauthorized use.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search