Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Masking SSN Expand / Collapse
Author
Message
Posted Friday, March 22, 2013 12:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 2:32 PM
Points: 22, Visits: 91
I am not certain how to mask first 5 digit of SSN w/in a column when the column has distinct values.

Here is a sample of my dataset. The column = Details.

Details
-------------
123-45-6789 John Doe
Applied Payment For 123-45-7689 John Doe
999-78-9909 Blue Sky
Applied Payment For 898-85-5895 Green River

If I use the following syntax:

Select 'XXX-XX-+Substring (Details,8,255) Details
From Test;

The result would look something like this:

Details
----------------------
XXX-XX-6789 John Doe
XXX-XX-ed Payment For 123-45-7689 John Doe
XXX-XX-9909 Blue Sky
XXX-XX-ed Payment For 898-85-5895 Green River

Instead, I'd like to know how to display the result into this format:

Details
--------------------
XXX-XX-6789 John Doe
Applied Payment For XXX-XX-7689 John Doe
XXX-XX-9909 Blue Sky
Applied Payment For XXX-XX-5895 Green River


Post #1434452
Posted Friday, March 22, 2013 12:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007
I understand the challenges here but why in the world do you have a table with SSN in them like this? This type of thing should not be in your database. It is one thing if you need store SSN (encrypted I hope) but it is another to have them thrown into comments like this.

I used the delimitedSplit8K function for this. To find the code for this function please follow the link in my signature about splitting strings.

;with SSN (RawData)
as
(
select '123-45-6789 John Doe' union all
select 'Applied Payment For 123-45-7689 John Doe' union all
select '999-78-9909 Blue Sky' union all
select 'Applied Payment For 898-85-5895 Green River'
)

select stuff(RawData, charindex(Item, RawData), 6, 'XXX-XX') as Fixed
from SSN
cross apply dbo.DelimitedSplit8K(RawData, ' ')
where Item like '%[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%'



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1434483
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse