Employers validating ssn


14-Feb-2019 05:21

The middle two numbers, referred to as the group number, have some additional specific rules. 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) Yeah, I couldn't resist.There also some specific numbers that are reserved and other that are not valid. It doesn't check for SSNs issued from Connecticut for people born and raised in Hawaii. - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon ROFL!!!! Read the article at Practices/61537/ for best practices on asking questions. Or, more honestly, I could have, but chose not to for the humor of it all.

To setup this test we will do the following: Load the data from the SSA website. To make this simpler for this test you can download this data that has been extracted already from the December 2010 file.

One of the first things you'll want to do after you enter the U. The Social Security office will mail your card after verifying your documents with DHS.

You can check on the status of your application by following up with your Social Security office either by phone or in person.

They can't start with 666, none of the groups can contain all 000 etc. :-P _______________________________________________________________Need help? - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

There are some other specific rules but I don't know them all off the top of my head. 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) SELECT *FROM ( VALUES ( '1'), ( '1'), ( '9'), ( '1'), ( '1'), ( '0') ) 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. 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 Rule2FROM ( VALUES ( '1'), ( '8'), ( '1'), ( '9'), ( '1'), ( '1'), ( '0') ) 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. For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw Need to Split some strings?We have never assigned an SSN with the first three digits of:000666900 series Additionally, prior to June 25, 2011, we never assigned an SSN with the first three digits of:000666Above 772 in the 700 series800 series900 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, last Name, first Name FROM employees WHERE ssn GROUP BY name 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.If you just want to validate the format this will work. 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) Figured I would dig a little and try to refresh my memory, it has been awhile since I had to deal with SSNs. _______________________________________________________________Need help? Read the article at Practices/61537/ for best practices on asking questions.