Remove SSN (Social Security Number) from a String in SQL.

Question

I have a string in a MS 2000 SQL db and need to remove just the SSN using the select satement. I don't have the option of using an external app. The other fun part is that the locaton of the SSN is not always the same.

[B-Day][First Name][SSN][POB]
12121970John123-45-6789Las Vages

Or

[B-Day][First Name][Last Name][SSN][POB]
12121970JohnDoe123-45-6789Las Vages

I need RegEx of the SQL variety!

Any ideas?

Answer 1

Assuming you have access to the server itself, here is an article which describes how to add a Regex function using VBScript to MSSQL 2000. From there the regex for matching a SSN is quite simple ([0-9]{3}-[0-9]{2}-[0-9]{4}). You may need to expand the function provided in the article to support replacing as well as matching.