April 17, 2008 at 10:05 am
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
April 17, 2008 at 10:39 am
Is this for some kind of testing system, or is this for some kind of security purposes in a production database?
April 17, 2008 at 10:43 am
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[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 17, 2008 at 10:47 am
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?
April 17, 2008 at 10:56 am
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[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 17, 2008 at 10:59 am
security purposes on a production database
April 17, 2008 at 11:05 am
So I assume you want some way to "unscramble" the phone numbers?
If that is the case, you probably want to look into encryption.
April 17, 2008 at 11:05 am
Oh - and Matt - that's pretty slick.
Hats off to you.
April 17, 2008 at 11:07 am
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?
April 17, 2008 at 11:14 am
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
April 17, 2008 at 11:26 am
GSquared (4/17/2008)
colemanj (4/17/2008)
security purposes on a production databaseIn 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.
Correct - or - just move the phone numbers (and any other "confidential info") into a separate table that is appropriately secure.
If you want to really encrypt this data, then don't use the built-in SQL encryption routines. Just like in really secure environments where you don't want a single person to have full access to a system, in order to REALLY secure data, the storage mechanism should not know how to decrypt or read the data.
----------------------------------------------------------------------------------
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?
April 17, 2008 at 12:08 pm
Im sorry maybe i should have stated that the database that I will be scrambling these phone numbers as well as other pieces of "Personal Data" is a copy of the production database. Management wants to take a copy of the Production weekly and copy it to a "training database" that they may use for DEMO purposes and training purposes and so theres the need for scrambling of sensitive data. So if i "losE" the original data...thats fine because in this database...its used for training and its refreshed every saturday morning.
Clear it up a little bit?
April 17, 2008 at 12:17 pm
Then Matt's solution looks pretty good.
April 17, 2008 at 12:21 pm
This should do what you want:
select right(convert(bigint,convert(varbinary(16),newid())),10)
April 17, 2008 at 12:24 pm
In that case, yeah, Matt's random solution will work. Or just "Hollywood" all the phone numbers by replacing them with "555-1234". Or "867-5309" (showing my age). Why spend all the CPU cycles on random numbers when anything at all will do, just so long as it's not real?
- 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
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply