Replace Wild Card Characters

[esilva002]

Hello, perhaps you guys have heard this before in the past, but here is
what I'm looking for.

I have a SQL 2005 table with Social security numbers. We need to get rid of the SSN numbers and replace them with SPLAT (*)
For example:

if the real SSN is: 340-53-7098 the repacement would be ***-**-****.

Sounds simply enough, but I can't seem to get it straight.

I need this to be created using an update query.

Again, any help would be appreciated it.

 

[markros]

SQL Server is not really good with string manipulation functions. Doing this task in another language and creating a CLR function may be a better option.

If you want to do this in T-SQL, take a look at chrtran function here

http://bradsruminations.blogspot.com/2010/01/handy-string-functions.html

PluralSight Learning Library

 

[SQLBill]

Are the SSN's stored alone in a column? Or are they included with other text?

If the SSN is the only thing in the column, I think you should just be able to do something like:

UPDATE tbl_PersonalData
SET SSNColumn = '***-**-****'
[\code]

-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[esilva002]

They are found with in a varchar(max) field! So I cant just update the column because other paragraphs of text are in there too.

 

[gmmastros]

My last thread was deleted, so I don't really feel like answering, certainly not with a lengthy explanation or a code sample, but...

You can do this with a combination of patindex and stuff.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

If your SSN is embedded inside the longer field, then I'd say, forget about T-SQL, you need to use RegEx.

Actually, I saw someone posted a link of T-SQL RegEx, but I haven't saved that link. I suggest you to google on T-SQL RegEx and see what comes up. Also, George made valid point, if all your SSN are in the same format
999-99-9999 then you can get them with the patindex, so may be you can do the job in T-SQL after all. I don't expect it to be quick, though.

PluralSight Learning Library