SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sql Query to find invalid SSN Numbers


Sql Query to find invalid SSN Numbers

Author
Message
Sean Lange
Sean Lange
SSC Guru
SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)SSC Guru (281K reputation)

Group: General Forum Members
Points: 281384 Visits: 19829
Jeff Moden - Tuesday, March 12, 2019 12:00 PM
glenn.rollins3 - Tuesday, March 12, 2019 11:36 AM
Jeff Moden - Tuesday, March 12, 2019 11:11 AM
Sean Lange - Tuesday, March 12, 2019 8:33 AM
glenn.rollins3 - Tuesday, March 12, 2019 8:02 AM

I've been using the following and it's proved successful.

WHERE LEN(SSN) <> 9
OR LEFT(SSN,1) IN ('8')
OR LEFT(SSN,2) IN ('74','78','79')
OR LEFT(SSN,3) IN ('666','734','735','736','737','738','739','773','774','775','776','777','778','779')
OR LEFT(SSN,3) = '000'
OR SUBSTRING(SSN,4,2) = '00'
OR SUBSTRING(SSN,6,4) = '0000'
--OR SSN IS NULL
OR SSN LIKE '%[a-z]%'

Thoughts?



I think that storing SSN in plain text is criminal behavior.

Preach it, Brother!!! Storing any form of Tax ID in an unencrypted fashion is just flat out wrong and a violation of every PII rule I've ever come across.

Thanks, I'll pass it on to the company's IT department.

No... That's almost useless. Pass it on to the CEO, the head of Marketing (while explaining the black eye to the company and resulting future lost business if there's a breach), the head of Finance (how much the breach will actually cost, which is more than you can currently imagine), AND the Director of IT (explain how they might not ever be able to find another job in IT if a breach occurs). If the company is public, pass all that bad news on to the Board of Directors, as well. The rank stupidity of things like clear text SSNs has got to stop.


How do you really feel about this Jeff? It is hard to be sure. LOL. I agree on all points!!!

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
roger.plowman
roger.plowman
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9865 Visits: 2039
Of course, you could always take the brute force approach. Smile

Create a table with all valid SSNs and set up a left join to it.

By today's standards a table of all valid SSNs wouldn't be that big (< 1 billion records of 1 int field as clustered index). Of course it might take a few days to generate, based on how many rules you want to include, but once finished you never have to do it again!

Kind of like a rainbow table used for the power of good!
Joe Celko
Joe Celko
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8440 Visits: 4309
roger.plowman - Wednesday, March 13, 2019 6:49 AM
Of course, you could always take the brute force approach. Smile

Create a table with all valid SSNs and set up a left join to it.

By today's standards a table of all valid SSNs wouldn't be that big (< 1 billion records of 1 int field as a clustered index). Of course, it might take a few days to generate, based on how many rules you want to include, but once finished you never have to do it again!

Kind of like a rainbow table used for the power of good!
As of a few years ago, the Social Security Administration did away with the original design of the Social Security number in favor of nine digits. There's no more grouping and other built-in information in the number. The problem was that they were running out of numbers, there were too many duplicates and forgeries (think illegal aliens and fake numbers), so they just gave up.

Medicare used to use the Social Security Number, that's been replaced by a string of 11 characters which mix digits and letters. Your best bet for validation is to subscribe to a service that will essentially do what you just said – keep a list in the database of what's been done.

http://www.veris-ssn.com/

Please post DDL and follow ANSI/ISO standards when asking for help.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)

Group: General Forum Members
Points: 975206 Visits: 49307
roger.plowman - Wednesday, March 13, 2019 6:49 AM
Of course, you could always take the brute force approach. Smile

Create a table with all valid SSNs and set up a left join to it.

By today's standards a table of all valid SSNs wouldn't be that big (< 1 billion records of 1 int field as clustered index). Of course it might take a few days to generate, based on how many rules you want to include, but once finished you never have to do it again!

Kind of like a rainbow table used for the power of good!


But first, stop storing the damned things in an unencrypted fashion. ;-)

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)

Group: General Forum Members
Points: 975206 Visits: 49307
Sean Lange - Tuesday, March 12, 2019 1:03 PM
Jeff Moden - Tuesday, March 12, 2019 12:00 PM
glenn.rollins3 - Tuesday, March 12, 2019 11:36 AM
Jeff Moden - Tuesday, March 12, 2019 11:11 AM
Sean Lange - Tuesday, March 12, 2019 8:33 AM
glenn.rollins3 - Tuesday, March 12, 2019 8:02 AM

I've been using the following and it's proved successful.

WHERE LEN(SSN) <> 9
OR LEFT(SSN,1) IN ('8')
OR LEFT(SSN,2) IN ('74','78','79')
OR LEFT(SSN,3) IN ('666','734','735','736','737','738','739','773','774','775','776','777','778','779')
OR LEFT(SSN,3) = '000'
OR SUBSTRING(SSN,4,2) = '00'
OR SUBSTRING(SSN,6,4) = '0000'
--OR SSN IS NULL
OR SSN LIKE '%[a-z]%'

Thoughts?



I think that storing SSN in plain text is criminal behavior.

Preach it, Brother!!! Storing any form of Tax ID in an unencrypted fashion is just flat out wrong and a violation of every PII rule I've ever come across.

Thanks, I'll pass it on to the company's IT department.

No... That's almost useless. Pass it on to the CEO, the head of Marketing (while explaining the black eye to the company and resulting future lost business if there's a breach), the head of Finance (how much the breach will actually cost, which is more than you can currently imagine), AND the Director of IT (explain how they might not ever be able to find another job in IT if a breach occurs). If the company is public, pass all that bad news on to the Board of Directors, as well. The rank stupidity of things like clear text SSNs has got to stop.


How do you really feel about this Jeff? It is hard to be sure. LOL. I agree on all points!!!


I know, huh? I can't believe that any company would be so stupid but there's constant evidence that it's actually a rampant problem. Heh... I guess that one came out loud too, huh? BigGrin

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
roger.plowman
roger.plowman
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9865 Visits: 2039
Jeff Moden - Thursday, March 14, 2019 9:29 PM
roger.plowman - Wednesday, March 13, 2019 6:49 AM
Of course, you could always take the brute force approach. Smile

Create a table with all valid SSNs and set up a left join to it.

By today's standards a table of all valid SSNs wouldn't be that big (< 1 billion records of 1 int field as clustered index). Of course it might take a few days to generate, based on how many rules you want to include, but once finished you never have to do it again!

Kind of like a rainbow table used for the power of good!


But first, stop storing the damned things in an unencrypted fashion. ;-)

Better yet, stop storing the damn thing AT ALL. Personally, the idea that an SSN is some super-sekret uber-key to steal somebody's identity is just STUPID, in and of itself. I get that it's got historical inertia, but when we depend on a *publicly used* ID to establish one's identity, along with other *publicly used* information, well, words fail me.

We've never correctly solved the authentication problem and may never, but until we do, requiring SSN for anything than its original purpose is just criminal.

Besides, isn't there a federal law prohibiting the use of SSN as an identification number?
patrickmcginnis59 10839
patrickmcginnis59 10839
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22599 Visits: 8784
Sean Lange - Tuesday, March 12, 2019 8:33 AM
glenn.rollins3 - Tuesday, March 12, 2019 8:02 AM

I've been using the following and it's proved successful.

WHERE LEN(SSN) <> 9
OR LEFT(SSN,1) IN ('8')
OR LEFT(SSN,2) IN ('74','78','79')
OR LEFT(SSN,3) IN ('666','734','735','736','737','738','739','773','774','775','776','777','778','779')
OR LEFT(SSN,3) = '000'
OR SUBSTRING(SSN,4,2) = '00'
OR SUBSTRING(SSN,6,4) = '0000'
--OR SSN IS NULL
OR SSN LIKE '%[a-z]%'

Thoughts?



I think that storing SSN in plain text is criminal behavior.

It still happens, I seem to remember supplying my SSN all over the place and could not avoid it if I didn't want to live like a hermit in a cave ;-) This means its subsequently shared with upstream servicers, etc, and that means its going to be processed, oftentimes as a lookup / matching key. This means storage, transmission, matching etc, so in my opinion its going to be a hard sale on trying to get routine processing of this data "criminalized".


to properly post on a forum:http://www.sqlservercentral.com/articles/61537/
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (434K reputation)SSC Guru (434K reputation)SSC Guru (434K reputation)SSC Guru (434K reputation)SSC Guru (434K reputation)SSC Guru (434K reputation)SSC Guru (434K reputation)SSC Guru (434K reputation)

Group: General Forum Members
Points: 434966 Visits: 44160
roger.plowman - Friday, March 15, 2019 6:50 AM
Jeff Moden - Thursday, March 14, 2019 9:29 PM
roger.plowman - Wednesday, March 13, 2019 6:49 AM
Of course, you could always take the brute force approach. Smile

Create a table with all valid SSNs and set up a left join to it.

By today's standards a table of all valid SSNs wouldn't be that big (< 1 billion records of 1 int field as clustered index). Of course it might take a few days to generate, based on how many rules you want to include, but once finished you never have to do it again!

Kind of like a rainbow table used for the power of good!


But first, stop storing the damned things in an unencrypted fashion. ;-)

Better yet, stop storing the damn thing AT ALL. Personally, the idea that an SSN is some super-sekret uber-key to steal somebody's identity is just STUPID, in and of itself. I get that it's got historical inertia, but when we depend on a *publicly used* ID to establish one's identity, along with other *publicly used* information, well, words fail me.

We've never correctly solved the authentication problem and may never, but until we do, requiring SSN for anything than its original purpose is just criminal.

Besides, isn't there a federal law prohibiting the use of SSN as an identification number?

The Social Security Act itself states that the SSN cannot be used for identification.


Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)

Group: General Forum Members
Points: 975206 Visits: 49307
Lynn Pettis - Friday, March 15, 2019 8:32 AM
roger.plowman - Friday, March 15, 2019 6:50 AM
Jeff Moden - Thursday, March 14, 2019 9:29 PM
roger.plowman - Wednesday, March 13, 2019 6:49 AM
Of course, you could always take the brute force approach. Smile

Create a table with all valid SSNs and set up a left join to it.

By today's standards a table of all valid SSNs wouldn't be that big (< 1 billion records of 1 int field as clustered index). Of course it might take a few days to generate, based on how many rules you want to include, but once finished you never have to do it again!

Kind of like a rainbow table used for the power of good!


But first, stop storing the damned things in an unencrypted fashion. ;-)

Better yet, stop storing the damn thing AT ALL. Personally, the idea that an SSN is some super-sekret uber-key to steal somebody's identity is just STUPID, in and of itself. I get that it's got historical inertia, but when we depend on a *publicly used* ID to establish one's identity, along with other *publicly used* information, well, words fail me.

We've never correctly solved the authentication problem and may never, but until we do, requiring SSN for anything than its original purpose is just criminal.

Besides, isn't there a federal law prohibiting the use of SSN as an identification number?

The Social Security Act itself states that the SSN cannot be used for identification.


Which is bewildering because the feds require it for tax transactions and tracking.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Joe Celko
Joe Celko
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8440 Visits: 4309
Lynn Pettis - Friday, March 15, 2019 8:32 AM
[quote]
roger.plowman - Friday, March 15, 2019 6:50 AM
[quote]
Jeff Moden - Thursday, March 14, 2019 9:29 PM
[quote]
roger.plowman - Wednesday, March 13, 2019 6:49 AM
The Social Security Act itself states that the SSN cannot be used for identification.
It is also illegal to murder people. How has that worked form you :-) ?


Please post DDL and follow ANSI/ISO standards when asking for help.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search