CODEWITHSUNDEEP
t-sqlsql-server-2008
user1901945
user1901945

Reputation: 33

How do I replace a SSN with a 9 digit random number in SQL Server 2008R2?

To satisfy security requirements, I need to find a way to replace SSN's with unique, random 9 digit numbers, before providing said database to a developer. The SSN is in a column in a table of a database. There may be 10's of thousands of rows in said table. The number does not need hyphens. I am a beginner with SQL and programming in general.

I have been unable to find a solution for my specific needs. Nothing seems quite right. But if you know of a thread that I have missed, please let me know.

Thanks for any help!

Upvotes: 3

Views: 11814

Answers (5)

Grant Lawrence
Grant Lawrence

Reputation: 1

If the requirement is to obfuscate a database then this will return the same unique value for each distinct SSN in any table preserving referential integrity in the output without having to do a lookup and translate.

       SELECT CAST(RAND(SSN)*999999999 AS INT)

Upvotes: 0

gring0
gring0

Reputation: 1

Not so fast, but easiest... I added some dot's...

DECLARE @tr NVARCHAR(40)
SET @tr = CAST(ROUND((888*RAND()+111),0) AS CHAR(3)) + '.' + 
    CAST(ROUND((8888*RAND()+1111),0) AS CHAR(4)) + '.' + CAST(ROUND((8888*RAND()+1111),0) AS
    CHAR(4)) + '.' + CAST(ROUND((88*RAND()+11),0) AS CHAR(2))
PRINT @tr

Upvotes: 0

MarkD
MarkD

Reputation: 5316

I've run a couple million tests in this and it seems to generate random (URN) 9 digit numbers (no leading zeros). I cannot think of a more efficient way to do this.

SELECT CAST(FLOOR(RAND(CHECKSUM(NEWID())) * 900000000 ) + 100000000 AS BIGINT)

The test used;

;WITH Fn(N) AS
(
    SELECT CAST(FLOOR(RAND(CHECKSUM(NEWID())) * 900000000 ) + 100000000 AS BIGINT)
    UNION ALL
    SELECT CAST(FLOOR(RAND(CHECKSUM(NEWID())) * 900000000 ) + 100000000 AS BIGINT)
    FROM Fn
)
,Tester AS
(
    SELECT TOP 5000000 *
    FROM Fn
)
SELECT   LEN(MIN(N))
        ,LEN(MAX(N)) 
        ,MIN(N)
        ,MAX(N)
FROM Tester
OPTION (MAXRECURSION 0)

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40359

If they do not have to be random, you could just replace them with ascending numeric values. Failing that, you’d have to generate a random number. As you may have discovered, the RAND function will only generate a single value per query statement (select, update, etc.); the work-around to that is the newid() function, which would generate a GUID for each row produced by a query (run SELECT newid() from MyTable to see how this works). Wrap this in a checksum() to generate an integer; modulus that by 1,000,00,000 to get a value within the SSN range (0 to 999,999,999); and, assuming you’re storing it as a char(9) prefix it with leading zeros.

Next trick is ensuring it’s unique for all values in your table. This gets tricky, and I’d do it by setting up a temp table with the values, populating it, then copying them over. Lessee now…

DECLARE @DummySSN as table
 (
   PrimaryKey  int      not null
  ,NewSSN      char(9)  not null
 )


--  Load initial values
INSERT @DummySSN 
 select
   UserId
  ,right('000000000' + cast(abs(checksum(newid()))%1000000000 as varchar(9)), 9)
 from Users

--  Check for dups
select NewSSN from @DummySSN group by NewSSN having count(*) > 1

--  Loop until values are unique
IF exists (SELECT 1 from @DummySSN group by NewSSN having count(*) > 1)
    UPDATE @DummySSN
     set NewSSN = right('000000000' + cast(abs(checksum(newid()))%1000000000 as varchar(9)), 9)
     where NewSSN in (select NewSSN from @DummySSN group by NewSSN having count(*) > 1)

--  Check for dups
select NewSSN from @DummySSN group by NewSSN having count(*) > 1

This works for a small table I have, and it should work for a large one. I don’t see this turning into an infinite loop, but even so you might want to add a check to exit the loop after say 10 iterations,

Upvotes: 2

Tahbaza
Tahbaza

Reputation: 9546

Here is one way.

I'm assuming that you already have a backup of the real data as this update is not reversible.

Below I've assumed your table name is Person with your ssn column named SSN.

UPDATE Person SET 
SSN = CAST(LEFT(CAST(ABS(CAST(CAST(NEWID() as BINARY(10)) as int)) as varchar(max)) + '00000000',9) as int)

Upvotes: 9

Related Questions