The closest thing the United States has to a universal identification number is the Social Security Number (SSN). You are supposed to validate the SSN when you hire a new employee, but a lot of programmers have no idea how to do it. I am not going to go into the privacy issues, recent laws, or anything to do with legal aspects. This is a column for working programmers, and all we care about is how to use these numbers in our code.

The Basics

The Social Security Number (SSN) is composed of three parts; all are in digits and separated by dashes in the format, “XXX-XX-XXXX.” These parts are called the Area, Group, and Serial.

Area numbers

For the most part, (there are a few exceptions), the Area is determined by where the individual applied for the Social Security Number (before 1972) or resided at time of application (after 1972). The chart below shows the Area numbers used in the United States and its possessions.

No SSNs with an Area number above 728 have been assigned in the 700 series, except for 729 through 733 and 764 through 772.

If an Area number is shown more than once, that means that certain numbers have been transferred from one state to another, or that an Area has been divided for use among certain geographic locations. The actual assignment is done based on the ZIP code given on the application (you can blame population shifts for this). You do not have to have a SSN to work in the United States. Since 1996, the IRS issued over eight million tax payer identification numbers to foreign workers without a SSN. In 2004 alone, there were 900,000 such numbers issued.

While 900-999 are not valid Area numbers, they were used for program purposes when state aid to the aged, blind and disabled was converted to a federal program administered by Social Security Administration. You might also see this range of Area numbers used to construct student ID numbers for foreign students in the days when schools used SSN as the student ID number.

Group numbers

The Group portion of the Social Security Number has no meaning other than to determine whether or not a number has been assigned. There was an Urban Myth that the ethnicity of the card holder was coded in the Group number, and I have no idea how that one got started. The Social Security Administration publishes a list of the highest group assigned for each Area once a month. You can download this data at http://www.ssa.gov/employer/highgroup.txt.

The only validation check on SSN is the way the Group numbers are issued. The first numbers issued are the odd numbers from 01 through 09, followed by the even numbers from 10 through 98, within each Area number. After all numbers in Group 98 of a particular area have been issued, Groups 02 through 08 (even numbers) are issued, followed by Groups 11 through 99 (odd numbers).

For example, if the highest group assigned for area XXX is 72, then we know that the number XXX-04-XXXX is an invalid Group number because even Groups under 9 have not yet been assigned.

Fifty or 60 years ago, wallets came with fake Social Security cards already in them to make them look good when they were on display - much like the photos of a family in a dime store picture frame that looks better than your real family. Many people simply used these fake cards. The numbers look valid, but the IRS and other government agencies have a list of them.

Serial numbers

The Serial portion of the Social Security Number has no meaning. The Serial number ranges from 0001 to 9999, but it is not assigned in strictly numerical order. The Serial number 0000 is never assigned.

Verifying Social Security Numbers

There are commercial firms and non-profit Web sites that will verify Social Security Numbers for living and deceased persons. They usually tell you if the person holding that number is alive or dead, along with the year and place of issue. Some of these sites are set up by government agencies or universities to help employers, hospitals, or other concerned parties validate SSNs. The commercial sites can do bulk validations from files that you submit to them, at a cost of about one cent per SSN.

Here is a small sample to get you started. Note: I am not recommending one source over another in this listing.

http://www.veris-ssn.com
http://www.searchbug.com/peoplefinder/ssn.aspx
http://privacy.cs.cmu.edu/dataprivacy/projects/ssnwatch/
http://info.dhhs.state.nc.us/olm/manuals/dma/eis/man/Eis1103.htm
http://www.comserv-inc.com/products/ssndtect.htm

--

Joe Celko was a member of the ANSI X3H2 Database Standards Committee and helped write the SQL-92 standards. He is the author of over 750 magazine columns and seven books, the best known of which is SQL for Smarties, now in its third edition. For more articles, puzzles, resources, and more, go to www.celko.com.