Validate SSN 3

[LarrySteele]

So I'm going to validate SSN's (9 numbers) coming in from an external source. I didn't find any isSSN functions out there, but came up with the following:

select length('123456789') || LENGTH(TRIM(TRANSLATE('123456789', '0123456789',' ')))
from dual;

This should return '9' for valid SSN's, and something else for invalid SSN's. I figure I'll just create an isSSN function. Before I do, wanted to see if there might be a better way to accomplish this.

Thanks,
Larry

 

[SantaMufasa]

Larry,

Let's first confirm your validation rules for SSNs. Here are a few questions that I have before asserting a user-defined SSN-validation function:[ul][li]Are SSN values coming in as[/li][ul][li]Nine consecutive numerals[/li][li]An Oracle number value?[/li][li]A character string with hyphens[/li][li]Or something else?[/li][/ul][li]Are you concerned about validating SSN against U.S. Social Security Administration validation rules? (e.g. just because the leading three digits are numeric does not mean you have a valid SSN.)[/li][/ul]These are a few of my concerns that I would want to address prior to writing code.


Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[LarrySteele]

Dave,

We're looking at really simplistic validation. A string of nine numbers only.

Thanks,
Larry

 

[Turkbear]

Hi,
Just to be Picky, why not name it IsNineNumbers since it does not validate the numbers as being a SSN?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[LarrySteele]

What's in a name?

IS_SSN is shorter than IS_NINE_NUMBERS.

 

[SantaMufasa]

We're looking at really simplistic validation. A string of nine numbers only.

So, Larry, are you saying it is a given that a string of nine numerals are your input? Is the input defined as "VARCHAR2(9)" or is the input defined as NUMBER(9)? This distinction makes significant difference in a user-defined, "IS_SSN" function.


Please advise.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[LarrySteele]

Dave,

The source is a text file, so they are nothing more than characters. The holding table I put them in is defined as CHAR(9). Really, honest, I'm just wanting to verify that the string is composed of exactly nine numbers, and nothing else.

Larry

 

[SantaMufasa]

Thanks, Larry...that answers enough questions for me to propose solution that, I believe, resolves your need.

My solution uses a user-defined function, "NUM_CHECK", that I have posted on other occasions here in the Oracle forums. The "NUM_CHECK" function accepts as an input argument, any string. It then returns that string as a NUMBER value if it is a valid number. It the string is not a valid number, the function returns a NULL.

So, in the solution below, I list:[ul][li]the code for NUM_CHECK[/li][li]the contents of a sample table con taining a VARCHAR SSN, "HR_DATA"[/li][li]A proof-of-concept SQL statement that illustrates the SSN validations and the output you were expecting.[/li][/ul]The functional narrative of the SSN-related SQL code (in order of execution) is:[ul][li]ltrim(ssn) - trim off any leading blank spaces.[/li][li]length(ltrim(ssn)) - determine the length of the trimmed string.[/li][li]decode(length(ltrim(ssn))... - Evaluate the length of that string, and...[/li][li]...9,to_char(num_check(ssn),'fm000000009') - If the length = 9, then execute my "num_check" function, transforming valid numbers into a 9-character numeric result; if, however, the length is not 9, then produce a NULL.[/li][li]nvl(...,'Invalid SSN: '||ssn) - If the previous result is NULL (due to either length issues or non-numeric-value issues), then output an error message in front of the SSN; otherwise output the valid SSN.[/li][/ul]:

create or replace function Num_check (x varchar2) return number is
    num_hold number;
begin
    num_hold := x;
    return num_hold;
exception
    when others then
        return null;
end;
/

Function created.

select * from hr_data;

LAST_NAME                 SSN
------------------------- ----------
Velasquez                  001234567
Ngao                       1024691
Nagayama                   203703701
Quick-To-See               30493A26A
Ropeburn                   406172835

select last_name
      ,nvl(decode(length(ltrim(ssn)),9,to_char(num_check(ssn),'fm000000009')),'Invalid SSN: '||ssn) SSN
  from hr_data
/

LAST_NAME                 SSN
------------------------- -----------------------
Velasquez                 001234567
Ngao                      Invalid SSN:  1024691
Nagayama                  203703701
Quick-To-See              Invalid SSN:  30493A26A
Ropeburn                  406172835

Let us know if this resolves your need and/or if you have any questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[LarrySteele]

Dave,

Interesting. Looks like we're not that far apart. The difference is yours returns SSN's, I just want to identify if an SSN is valid.

In going over mine again, I noticed I had an extra LENGTH() function wrapping the second half. After removing that, the line becomes:

length('123456789') || TRIM(TRANSLATE('123456789', '0123456789',' '))

No difference in outcome, but anytime I can shave off a function, I consider it a good thing.

Here's how I see this working...

insert into bad_ssn
select *
from blah
where length(blah.ssn) || TRIM(TRANSLATE(blah.ssn, '0123456789',' ')) != 9;

delete
from blah
where length(blah.ssn) || TRIM(TRANSLATE(blah.ssn, '0123456789',' ')) != 9;

Or something to that effect. Just quickly scribbled the SQL just minutes before starting holiday, so if there's obvious SQL errors... Let's just say my brain's already out the door, and I better head after it!

Dave, thanks again for the assist. By the way, I really like how you explained the code so anyone reading this post will be able to understand. Have a star for that.

Larry

 

[SantaMufasa]

Thanks, Larry, for the

I just want to identify if an SSN is valid.

To use NUM_CHECK to validate SSN, all you need to do is place the function invocation in a conditional expression:

...WHERE num_check(ssn) is NULL -- to list invalid SSNs, or
...WHERE num_check(ssn) is not NULL -- to list [b]valid[/b] SSNs

Let us know if this meets your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Thargy]

Larry,

I can't resist the temptation to start a naming war, so I just have to offer my (obviously superior) version of things, with a much better name .

CREATE OR REPLACE FUNCTION SSN_IS_VALID (ssn_in IN VARCHAR2) 
RETURN PLS_INTEGER
IS

RETVAL PLS_INTEGER DEFAULT 0;

BEGIN
    --Only if there are no spaces in the string, is it ok to check number validity
    IF  (INSTR(ssn_in,' ') = 0) AND (TO_NUMBER(ssn_in) BETWEEN 0 AND 999999999) THEN
        RETVAL := 1;
    ELSE
        RETVAL := 0;
    END IF;
    
    RETURN RETVAL;
    
EXCEPTION
    WHEN OTHERS THEN RETURN 0;
END;

Populate a table with Santa's generously provided test data.

CREATE TABLE LARRY_TEST
(LAST_NAME VARCHAR2(50),
 SSN       CHAR(9)
 );

INSERT INTO LARRY_TEST (LAST_NAME,SSN) VALUES ('Velasquez','001234567');
INSERT INTO LARRY_TEST (LAST_NAME,SSN) VALUES ('Ngao','1024691');
INSERT INTO LARRY_TEST (LAST_NAME,SSN) VALUES ('Nagayama','203703701');
INSERT INTO LARRY_TEST (LAST_NAME,SSN) VALUES ('Quick-To-See','30493A26A');
INSERT INTO LARRY_TEST (LAST_NAME,SSN) VALUES ('Ropeburn','406172835');

SELECT * FROM LARRY_TEST WHERE SSN_IS_VALID(SSN) = 1; yields

LAST_NAME,SSN
Velasquez,001234567
Nagayama,203703701
Ropeburn,406172835

I know from other posts that you have access to versions of Oracle later than 9i. I have a suggestion involving DBMS_ERRLOG which is only available in 10 or above. The reason I mention it is that it involves no code, just DDL, can be easily tested, and rerun time and again. I think these advantages are sufficient to warrant considering the use of a later version of Oracle.

Build a table to hold test data, validated data and log any validation errors.

CREATE TABLE LARRY_VALIDATED
(
  VALIDATED_COLUMN  VARCHAR2(9 BYTE),
  DUMMY_COLUMN1     VARCHAR2(30 BYTE)
);

ALTER TABLE LARRY_VALIDATED
ADD CONSTRAINT NN_LARRY_VALIDATED_COLUMN
CHECK (VALIDATED_COLUMN IS NOT NULL);

ALTER TABLE LARRY_VALIDATED
ADD CONSTRAINT NO_WHITE_SPACES_IN_VALIDATED
CHECK (INSTR(VALIDATED_COLUMN,' ') = 0);

ALTER TABLE LARRY_VALIDATED
ADD CONSTRAINT NINE_CHARACTERS_PRESENT
CHECK (LENGTH(VALIDATED_COLUMN) = 9);

ALTER TABLE LARRY_VALIDATED
ADD CONSTRAINT DIGITS_ZERO_TO_NINE_ONLY
CHECK (TO_NUMBER(VALIDATED_COLUMN) BETWEEN 0 AND 999999999);


CREATE TABLE LARRY_TEST (
  VALIDATED_COLUMN  VARCHAR2(9 BYTE),
  DUMMY_COLUMN1     VARCHAR2(30 BYTE)
);

EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('LARRY_VALIDATED','LARRY_VALIDATED_ERRORS');

You now have unit test data, a table for valid data, and a table with duds in it.

Prepare the unit tests as follows:-

--Unit test the validation
INSERT INTO LARRY_TEST (VALIDATED_COLUMN, DUMMY_COLUMN1) VALUES ('123456789','Valid nine digits');
INSERT INTO LARRY_TEST (VALIDATED_COLUMN, DUMMY_COLUMN1) VALUES (NULL,'Missing SSN');
INSERT INTO LARRY_TEST (VALIDATED_COLUMN, DUMMY_COLUMN1) VALUES (' 12345678','Leading white space');
INSERT INTO LARRY_TEST (VALIDATED_COLUMN, DUMMY_COLUMN1) VALUES ('12345678 ','Trailing white space');
INSERT INTO LARRY_TEST (VALIDATED_COLUMN, DUMMY_COLUMN1) VALUES ('1234 5678','Contains white space');
INSERT INTO LARRY_TEST (VALIDATED_COLUMN, DUMMY_COLUMN1) VALUES ('12345678','Not enough characters');
INSERT INTO LARRY_TEST (VALIDATED_COLUMN, DUMMY_COLUMN1) VALUES ('00001exp2','Valid number - not nine digits');
COMMIT;

and then run the tests with the just-inserted values.

INSERT INTO LARRY_VALIDATED SELECT * FROM LARRY_TEST LOG ERRORS INTO LARRY_VALIDATED_ERRORS REJECT LIMIT UNLIMITED;

check for good records with

SELECT * FROM LARRY_VALIDATED;

and check for duds with

SELECT * FROM LARRY_VALIDATED_ERRORS;

which in this case yields

ORA_ERR_MESG$                                                             VALIDATED_COLUMN DUMMY_COLUMN1     
ORA-02290: check constraint (SCOTT.NN_LARRY_VALIDATED_COLUMN) violated                     'Missing SSN'     
ORA-02290: check constraint (SCOTT.NO_WHITE_SPACES_IN_VALIDATED) violated ' 12345678'      'Leading white space'     
ORA-02290: check constraint (SCOTT.NO_WHITE_SPACES_IN_VALIDATED) violated '12345678 '      'Trailing white space'            
ORA-01722: invalid number                                                 '1234 5678'      'Contains white space'            
ORA-02290: check constraint (SCOTT.NINE_CHARACTERS_PRESENT) violated      '12345678'       'Not enough characters'           
ORA-01722: invalid number                                                 '00001exp2'      'Valid number - not nine digits'

I omitted some of the columns from the error report due to the width of the page being limited and for clarity. However, you can see that oracle will automatically list each and every error for you, with no effort on your part.

Regards

T

 

[LarrySteele]

T - that goes way beyond our needs for this project. However, that's one I'm putting in my toolbox. Have a star for some nice examples.

 

[Thargy]

Larry,

thanks - I like purple pointy thingies.

If you define the text file as an external table, you don't have to load it into oracle. You can just point the sql provided by Santa at the "table" and read the dud records. This should save the hassle of a staging table, and will likely speed up the process a tad too.

Regards

T

 

[LarrySteele]

If you define the text file as an external table, you don't have to load it into oracle.

Yes, however, that against IT's standard practice. And that's not negotiable. No worries, I got what I need.

Thanks again.

 

[ChrisHunt]

Coming a bit late to this one, but it seems to me that you could check for "does this string consist of exactly nine digits" like this:

SELECT *
FROM   larry_test 
WHERE  TRANSLATE(ssn,'1234567890','9999999999') = '999999999'

TRANSLATE() is a really handy function for checking input against a format. If you wanted to write a routine to check the British equivalent, which include letters as well as numbers, you could do so like this:

SELECT *
FROM   some_other_test 
WHERE  TRANSLATE(ni_number,
                 '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ',
                 '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX')             IN ('XX999999X','XX999999')

(Assuming Wikipedia is to be trusted when it says that the letter on the end of NI numbers is optional).


-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[LarrySteele]

Chris,

You may be late to the game, but you've got the winning ticket. I knew there was a simpler method. Now that it's written, I can't believe I didn't see it, it's quite logical.

So, after dropping two functions and a concatenation, the criteria becomes...

insert into bad_ssn
select *
from blah
where TRANSLATE(blah.ssn, '0123456789','999999999') != '999999999';

delete
from blah
where TRANSLATE(blah.ssn, '0123456789','999999999') != '999999999';

Much better.

Thanks again Chris. Have a star on me.

 

[Beilstwh]

One other point in this discussion. Please do not use CHAR(9) in your holding table. While CHAR is still available because of standards, use VARCHAR2(9). It is much better. For example if you were using char to hold the ssn, it would always show a length of 9 even if it is blank. Varchar2 will strip trailing blanks unless you force them in.

Bill
Lead Application Developer
New York State, USA

 

[SantaMufasa]

if you were using char to hold the ssn, it would always show a length of 9 even if it is blank.

Actually, Bill, if a fixed-length column contains nothing (i.e., NULL), Oracle makes an exception to its "fixed-length" behavior. As you see, below, a NULL in a CHAR(9) column does not consume 9 characters...Oracle stores it in virtually the same way as a NULL in a VARCHAR2 column:

create table abc (x char(9));
insert into abc values (null);
insert into abc values ('x');
select x data, length(x) length, dump(x) dump from abc;

DATA          LENGTH DUMP
--------- ---------- -----------------------------------------
                     NULL
x                  9 Typ=96 Len=9: 120,32,32,32,32,32,32,32,32

But I concur that VARCHAR2 is far superior to any use of CHAR...I have yet to identify a valid/superior business or technical reason to use Oracle's CHAR column definition.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Beilstwh]

I agree with your point, I meant 9 spaces, not null. but your point is will taken.

Bill
Lead Application Developer
New York State, USA