|
|
Posted by DickChristoph on 04/01/06 00:41
Hi
Having nothing better to do this afternoon I wrote a function called
CodeDecodeSSN which has the advantage that it can be used to go from SSN to
Encrypted SSN and then from Encrypted SSN back to the originalSSN.
It has the disadvantage that the Encrypted SSN may contain Hex characters A
through F).
It probably is also slow.
The mask is arbitrary, you can select any number of the form ###-##-####'
(these must be numeric digits 0 to 9) but it must remain the same through
the life of the application.
Modify this line to set your own mask.
>set @mask = '123-45-6789'
Basically the function works by doing a bitwise XOR of each digit in the
Source SSN against the corresponding digit in the @mask.
Using a hash code such as David suggested is probably a better idea, but
like i said, I had nothing better to do this afternoon. ( I wish someone
would offer me a job :) )
select dbo.CodeDecodeSSN('123-46-7890')
Produces 000-03-1F19
select dbo.CodeDecodeSSN('000-03-1F19')
Produces 123-46-7890
create Function CodeDecodeSSN(@src varchar(11))
returns varchar(11)
begin
declare @mask varchar(11)
declare @rv varchar(11)
set @mask = '123-45-6789'
declare @i int
declare @j int
declare @c int
declare @c1 char(1)
declare @c3 char(3)
declare @m int
set @i = 1
set @rv = ''
while @i <= 11
begin
if @i = 4 or @i = 7
set @rv = @rv + '-'
else
begin
Set @c3 = '%' + substring(@src,@i,1) + '%'
set @c = PatIndex(@c3,'0123456789ABCDEF') -1
Set @m = substring(@mask,@i,1)
set @c = @c ^ @m
if @c > 9
begin
set @c1 = char(ascii('A') + @c - 10)
end
else
begin
set @C1 = cast(@c as char(1))
end
set @rv = @rv + @c1
end
set @i = @i + 1
end
return @RV
end
--
-Dick Christoph
<ILCSP@NETZERO.NET> wrote in message
news:1143739733.710415.9180@z34g2000cwc.googlegroups.com...
> 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.
>
> For example:
>
> if the real SSN is: 340-53-7098 the MemberID would be 451-64-8109.
>
> Sounds simply enough, but I can't seem to get it straight.
>
> I need this number to be created using a query, as this query is a
> report's record source.
>
> Again, any help would be appreciated it.
>
Navigation:
[Reply to this message]
|