Finding a SSN in a text area

Question

I have a huge VARCHAR field, that is a body of an email. There could be any sort of text on either side of the email. I want to see if anyone listed their Social security number anywhere in the text.

In the WHERE clause, I tried

WHERE X.Description LIKE '%___-__-____%'

Is there a way to find a numeric string that looks like 111-11-1111 or 111 11 1111

Which DBMS are you using? And if you find that pattern how can you be certain it is an SSN? — Sean Lange, Mar 21 '18 at 19:33
I.e. are you using MySQL or MS SQL Server? The answer will not be the same for those two products... — jarlh, Mar 21 '18 at 20:01
SSNs might be separated by spaces and not by hyphens in text. — Gordon Linoff, Mar 21 '18 at 20:02

score 1 · Answer 1 · answered Mar 21 '18 at 19:36

use regex

How to detect if a string contains at least a number?

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql

WHERE X.Description LIKE ''[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'

T-SQL Regex for social security number (SQL Server 2008 R2)

score 1 · Answer 2 · answered Mar 21 '18 at 19:37

1

WHERE X.Description LIKE '%[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%'

answered Mar 21 '18 at 19:37

Xedni

3,662
2
16
27

score 1 · Answer 3 · answered Mar 21 '18 at 20:03

I don't think you can guarantee that the separator for SSNs is always a hyphen. In SQL Server, you could find examples using like:

where X.Description LIKE '%[0-9][0-9][0-9][- ][0-9][0-9][0-9][- ][0-9][0-9][0-9][0-9]%'

In MySQL, you would use regular expressions:

where X.Description regexp '[0-9]{3}[- ][0-9]{3}[- ]{4}'

score 1 · Answer 4 · answered Mar 21 '18 at 20:05

1

I would add a space between each bracket and % to be safe like this:

WHERE X.Description LIKE '% [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9] %'

Otherwise the code would incorrectly think something like this is an SSN: 12343444-76-878787878

answered Mar 21 '18 at 20:05

Daniel Marcus

2,686
1
7
13

Finding a SSN in a text area

4 Answers4