Sql Query to find invalid SSN Numbers

  • mwaniki1

    SSC-Addicted

    Points: 450

    I would like to write a query that returns any Employees (id, ssn, and name) with an invalid SSN along with the company name. From the following two tables information.

    tblCompany

    co (PK, varchar(10), not null)

    name (varchar (50), not null)

    tblEmployees

    co (PK, FK, varchar(10), not null)

    id (PK, varchar(10), not null)

    ssn (varchar(11), not null)

    lastName (varchar(40), not null)

    firstName (varchar(40), not null)

    An invalid or impossible SSN is one that we never assigned.

    We have never assigned an SSN with the first three digits of:

    000

    666

    900 series

    Additionally, prior to June 25, 2011, we never assigned an SSN with the first three digits of:

    000

    666

    Above 772 in the 700 series

    800 series

    900 series

    We have never assigned an SSN with the second two digits of 00 or the last four digits of 0000.

    What i have so far is;

    SELECT id, ssn, lastName, firstName

    FROM employees

    WHERE ssn

    GROUP BY name

  • Steve Cullen

    SSCertifiable

    Points: 5598

    WHERE ssn NOT LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'

    will check that all data is numeric with the dashes in the right place. Beyond that, you need to specify any other validation rules.

    Converting oxygen into carbon dioxide, since 1955.
  • eccentricDBA

    SSCarpal Tunnel

    Points: 4844

    Looks like Steve beat me to this... However, here is a quick test for what Steve is showing.

    -- Define the tables

    DECLARE @tblCompany TABLE (co varchar(10), name varchar (50));

    DECLARE @tblEmployees TABLE (co varchar(10),id varchar(10), ssn varchar(12),lastName varchar(40), firstName varchar(40));

    -- Populate the tables

    INSERT @tblCompany (co, name) VALUES ('A','Company A');

    INSERT @tblCompany (co, name) VALUES ('B','Company B');

    INSERT @tblEmployees (co, id, ssn, lastName, firstName) VALUES ('A',1,'123-23-1234','Tester', 'Joe');

    INSERT @tblEmployees (co, id, ssn, lastName, firstName) VALUES ('A',2,'23121-2341','Tester', 'Jane');

    INSERT @tblEmployees (co, id, ssn, lastName, firstName) VALUES ('A',3,'312-22-3412','User', 'John');

    INSERT @tblEmployees (co, id, ssn, lastName, firstName) VALUES ('A',4,'234234123','User', 'Janet');

    INSERT @tblEmployees (co, id, ssn, lastName, firstName) VALUES ('B',5,'342-2N-3234','Manager', 'Sam');

    INSERT @tblEmployees (co, id, ssn, lastName, firstName) VALUES ('B',6,'432-254234','Tiger', 'Scott');

    -- Verify the data

    SELECT * FROM @tblCompany

    SELECT * FROM @tblEmployees

    -- First Basic Check

    SELECT * FROM @tblEmployees WHERE ssn NOT LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'

    And here is the books online information about it.

    http://msdn.microsoft.com/en-us/library/ms179859(v=sql.105).aspx

  • Sean Lange

    SSC Guru

    Points: 286400

    This is only a regular expression and does not verify that the number is in fact valid.

    The middle two numbers, referred to as the group number, have some additional specific rules. There also some specific numbers that are reserved and other that are not valid. They can't start with 666, none of the groups can contain all 000 etc. There are some other specific rules but I don't know them all off the top of my head.

    If you just want to validate the format this will work. If you want to verify that the numbers is in fact valid you will probably want to use CLR because the rules are incredibly complex.

    _______________________________________________________________

    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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Lynn Pettis

    SSC Guru

    Points: 442116

    If I also remember, the SSN 555-55-5555 is invalid.

    Not sure about other single repeating values, but the one above is usually used in various illustrations.

  • Sean Lange

    SSC Guru

    Points: 286400

    Figured I would dig a little and try to refresh my memory, it has been awhile since I had to deal with SSNs.

    http://en.wikipedia.org/wiki/Social_Security_number

    _______________________________________________________________

    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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • GSquared

    SSC Guru

    Points: 260824

    Something like this will work:

    SELECT *

    FROM ( VALUES ( '123-45-6789'), ( '123-54-6789'), ( '923-00-1234'), ( '123-00-1234'), ( '123-45-0000'),

    ( '000-12-1234') ) AS V (SSN)

    WHERE SSN LIKE '000-__-____'

    OR SSN LIKE '___-00-____'

    OR SSN LIKE '___-__-0000'

    OR SSN LIKE '666-__-____'

    OR SSN LIKE '9[0-9][0-9]-__-____' ;

    You'd have to have an issue-date (might be able to use date-of-birth, but it's not really valid) for the rules about those issued before a certain date. Otherwise, it could be legit or could be not.

    Could do something like:

    SELECT *,

    CASE WHEN SSN LIKE '77[3-9]-__-____' THEN 'Check Date-of-Issue'

    WHEN SSN LIKE '7[8-9]_-__-____' THEN 'Check Date-of-Issue'

    WHEN SSN LIKE '8__-__-____' THEN 'Check Date-of-Issue'

    ELSE 'Invalid'

    END AS Rule2

    FROM ( VALUES ( '123-45-6789'), ( '823-54-6789'), ( '123-54-6789'), ( '923-00-1234'), ( '123-00-1234'), ( '123-45-0000'),

    ( '000-12-1234') ) AS V (SSN)

    WHERE SSN LIKE '000-__-____'

    OR SSN LIKE '___-00-____'

    OR SSN LIKE '___-__-0000'

    OR SSN LIKE '666-__-____'

    OR SSN LIKE '9__-__-____'

    OR SSN LIKE '8__-__-____'

    OR SSN LIKE '7[8-9]_-__-____'

    OR SSN LIKE '77[3-9]-__-____' ;

    If it finds a positive rule-break, it says "Invalid", but if it's just a possible rule-break, depending on date, it says so.

    That only covers the rules mentioned in the post. It doesn't check for malformed SSNs. It doesn't check for duplicate SSNs. It doesn't check for SSNs issued from Connecticut for people born and raised in Hawaii. And so on.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Sean Lange

    SSC Guru

    Points: 286400

    It doesn't check for SSNs issued from Connecticut for people born and raised in Hawaii.

    ROFL!!!! 😛

    _______________________________________________________________

    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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • GSquared

    SSC Guru

    Points: 260824

    Sean Lange (4/25/2012)


    It doesn't check for SSNs issued from Connecticut for people born and raised in Hawaii.

    ROFL!!!! 😛

    Yeah, I couldn't resist. Or, more honestly, I could have, but chose not to for the humor of it all.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • CapnHector

    SSCoach

    Points: 16083

    GSquared (4/25/2012)


    Sean Lange (4/25/2012)


    It doesn't check for SSNs issued from Connecticut for people born and raised in Hawaii.

    ROFL!!!! 😛

    Yeah, I couldn't resist. Or, more honestly, I could have, but chose not to for the humor of it all.

    ok lets leave the politics for the anything else forum.


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • Sean Lange

    SSC Guru

    Points: 286400

    Well he just using that as an example that it does not validate the state of origin of the SSN. I have no idea what you mean about politics. 😉

    _______________________________________________________________

    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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • CapnHector

    SSCoach

    Points: 16083

    Sean Lange (4/25/2012)


    Well he just using that as an example that it does not validate the state of origin of the SSN. I have no idea what you mean about politics. 😉

    ROTFLMAO


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • Lynn Pettis

    SSC Guru

    Points: 442116

    To what level of validation are you attempting to complete?

  • mwaniki1

    SSC-Addicted

    Points: 450

    Basically looking to use a JOIN on the two tables to find invalid ssn using the invalid rules listed.

  • Lynn Pettis

    SSC Guru

    Points: 442116

    mwaniki1 (4/27/2012)


    Basically looking to use a JOIN on the two tables to find invalid ssn using the invalid rules listed.

    You keep saying join to validate. What are you joining to what to validate ssn? From what I see, you have two tables (Company and Employee) and there is nothing in either with which to validate the SSN against. Your query has to validate the SSNs and report which ones are invalid.

Viewing 15 posts - 1 through 15 (of 45 total)

You must be logged in to reply to this topic. Login to reply