Posted by David Portas on 03/30/06 22:10

ILCSP@NETZERO.NET wrote:
> Hello, perhaps you guys have heard this before in the past, but here is
> what I'm looking for.
>
> I have a SQL 2000 table with Social security numbers. We need to
> create a Member ID using the Member's real SSN but since we are not
> allowed to use the exact SSN, we need to add 1 to each number in the
> SSN. That way, the new SSN would be the new Member ID.
>

That's almost totally ineffective if the idea is to protect the SSN
from disclosure. Why don't you use a hash of the number instead? A
secure one like SHA1 for example.

If you must, then something like this should do it:

REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE(@ssn,'0','#')
,'9','0'),'8','9'),'7','8'),'6','7'),'5','6')
,'4','5'),'3','4'),'2','3'),'1','2'),'#','1')

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]