March 22, 2013 at 12:03 pm
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
March 22, 2013 at 12:56 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply