Re: ssn test in SQL/PLSQL

TurkBear wrote in message <38293503.70440237_at_super.news-ituk.to>... <snip>
>What does 'valid ssn number' mean?

>Bob <bookouri_at_my-deja.com> wrote:

<snip>
>>Does anyone have a procedure/function to test a char field for a valid
>>SSN number. I need to produce a text file which includes SSN for each
>>row, but in rows that do not have valid ssn I need to replace the
>>invalid data with '000000000'.

<snip>

This is actually much harder than you imagine, and you need a fairly large-ish internal table to handle the issued vs. non-issued part. But here are the simple parts:

1. substr(SSN, 1, 3) not = '000'
2. substr(SSN, 4, 2) not = '00'
3. substr(SSN, 6, 4) not = '0000'
4. SSN < 730010001 (I think, or maybe 729xxxxxx)
5. SSN-issued check: you need a table of high SSN issuances by region (state or territory) to do this, plus some semi-convoluted logic to figure out the valid groups (positions 4 and 5) for each range. The Social Security Administration (SSA) posts the upcoming high ranges every quarter, so you have to have a process in place to update this. As a fraud control measure, be aware that SSA posts the new high ranges *before* they start issuing SSNs in that range, so a well-informed perpetrator could still slip something past you.
6. Deceased SSN check, if applicable. If you keep records regarding dead people, you probably have to bypass this check. You will have to work through the Social Security Administration to get this information, and that is not a trivial undertaking.
7. Goofy SSN check - nixie the 111-11-1111, 111-22-3333, 123456789, et cetera entries.

The SSA will do (5) and (6) for you annually if you send them a file. Public agencies dealing with fraud detection and control have a little more leeway here. The best approach is probably to send SSA a file to determine if you have any problems first.

The SSA web site is a very good resource to get started in this.

I have done this in IBM mainframe batch processing environments, but not in Oracle (yet). Received on Thu Nov 11 1999 - 00:47:38 CST