• roger.plowman - Wednesday, March 13, 2019 6:49 AM

    Of course, you could always take the brute force approach. 🙂

    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.