Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have sensitive information (names of people) and we're looking to put into place a script to "scrub" of the names. Has anybody come up with a good algorithm to do this, something other than just making all of the last names "TEST" or something?

Thanks.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

I had to do this for NPI (non-public information) reasons at a previous employer. Ended up scrambling fields in a script, since a first name / last name pair isn't NPI unless it's tied to something else.

Here's a rough version of the script. Season to taste. Also, this assumes you have first and last names in separate fields.

CREATE TABLE #RandomData
(
    DataID int not null,
    ID int not null,
    FirstName varchar(50),
    LastName varchar(50)
)

INSERT INTO #RandomData (ID, DataID)
SELECT ROW_NUMBER() OVER (ORDER BY NEWID()), DataId FROM DataTable

UPDATE r SET r.FirstName = x.FirstName
FROM #RandomData r
INNER JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ID, FirstName FROM DataTable
) x
    ON x.ID = r.ID

UPDATE r SET r.LastName = x.LastName
FROM #RandomData r
INNER JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ID, LastName FROM DataTable
) x
    ON x.ID = r.ID

CREATE CLUSTERED INDEX PK_RandomData ON #RandomData
(
    DataID asc
)

ALTER TABLE DataTable DISABLE TRIGGER ALL

UPDATE dt 
    SET dt.FirstName = r.FirstName, 
    dt.LastName = r.LastName
FROM DataTable dt
INNER JOIN #RandomData r
    ON dt.DataId = r.DataId

ALTER TABLE DataTable ENABLE TRIGGER ALL

DROP TABLE #RandomData
share|improve this answer
    
That's pretty inventive, thanks. –  Brian Mains Feb 1 '12 at 17:31
    
This works very well, nice job. –  Tom yesterday

If you don't want to know what the name is but for whatever reason you want to keep something in the table for that record, you can always just use a hash of the name:

UPDATE MyTable
SET Name = HASHBYTES('SHA1', Name)

This will keep a unique value for each name record, but you will have no way to know what the name is. You can, however, see if a name matches a value by comparing an hashed input to the stored hash.

Cryptographic hashes like this are one way, so you can create an identical hash from an identical input string, but not determine the input string even if you know what the hash is.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.