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

SELECT substring(a, 1, PatIndex('%[0-9][0-9][0-9]-%', a) - 3)  
+ substring(a, PatIndex('%[0-9][0-9][0-9]-%', a) + 9, 100) FROM myTable

myTable is the table, "a" is the name of the column.

EDIT: Ofcourse there is some assumption regarding social security number format. Also, I have added an arbitrary number 100 to pick the string to the end. You can increase it if your string is more than that.