Scrambling a SSN or lastname field

Last Post 17 Apr 2002 11:06 AM by bmccaig. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
bmccaig
New Member
New Member

--
11 Apr 2002 12:32 AM
Hello ,
I have been tasked to sanitize an application db(SQL Server) and scramble a couple of fields like Social Security Number, Firstname, Lastname, and EMployee ID.
This is for a demo version of a production db for a trade show.

Assuming all the fields are character.

Can someone suggest an algorirthym that is easy to create and run fast the tables I have to modify are about 70K rows and there are about 20 tables.

Example of what I mean by scrambling:
Assume it is all character data and I want a SSN 991-09-2323 to become 323-49-9988 and the Name John Jones to become John [keep the first name the same]Kppft you get the idea.

Thanks in advance for any ideas.

Brian

My email is bmccaig@guthrie-thorn.com
Lee
New Member
New Member

--
17 Apr 2002 10:20 AM
SQL Server doesn't have this feature. I wrote an Extended Procecure in C that 'scrambles' values in table, but a utility that is rich to do this in SQL doesn't exist.

Lee Everest
MCP, MCDBA
bmccaig
New Member
New Member

--
17 Apr 2002 11:06 AM
Hi Lee,

Thanks for the reply.

I actually accomplished the SSN scramble by extracting the three parts of the SSN as character strings, then CAST strings to float, multipy by a Random number(you can use RANDOM or I created one based on the Datetime) then CAST the Float value back to Character. Then assemble the three parts to form the whole SSN. This technique worked for me.
In terms of the First and Lastnames, I used a simple CASE statement and created a translation table to process each character and translate it to someother character. I did the same for the employee ID.

The code was not pretty, but it worked and I met the sales demo deadline....

Brian McCaig
MCT,MCSE,MCDBA,CTT+
Lee
New Member
New Member

--
07 May 2002 04:59 PM
Sounds good. Were you able to 'descramble' at will?
You are not authorized to post a reply.

Acceptable Use Policy