Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Random Phone numbers Expand / Collapse
Author
Message
Posted Thursday, April 17, 2008 10:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 13, 2009 8:40 AM
Points: 7, 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
Post #486587
Posted Thursday, April 17, 2008 10:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
Is this for some kind of testing system, or is this for some kind of security purposes in a production database?
Post #486619
Posted Thursday, April 17, 2008 10:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:50 PM
Points: 2,717, Visits: 3,854
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
Post #486621
Posted Thursday, April 17, 2008 10:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:56 PM
Points: 7,075, Visits: 15,325
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?
Post #486623
Posted Thursday, April 17, 2008 10:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:50 PM
Points: 2,717, Visits: 3,854
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.


______________________________________________________________________

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
Post #486631
Posted Thursday, April 17, 2008 10:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 13, 2009 8:40 AM
Points: 7, Visits: 33
security purposes on a production database
Post #486633
Posted Thursday, April 17, 2008 11:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
So I assume you want some way to "unscramble" the phone numbers?
If that is the case, you probably want to look into encryption.
Post #486639
Posted Thursday, April 17, 2008 11:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
Oh - and Matt - that's pretty slick.

Hats off to you.
Post #486641
Posted Thursday, April 17, 2008 11:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:56 PM
Points: 7,075, Visits: 15,325
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?
Post #486643
Posted Thursday, April 17, 2008 11:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #486648
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse