Code Dump: NPI PL/SQL Validation

I just want to throw this out there since it's useful. I searched and didn't come up with an Oracle specific implementation of this algorithm, so here it is. This function checks if a given National Provider Identifier(NPI) is valid. The Luhn Algorithm is used to validate NPI after a prefix of "80840" is applied.

This code is provided as is. I'm not making any guarantees that it's 100% right and I'm not responsible if anything bad happens as a result of your use of this. So, don't yell at me if small animals are harmed with the use of this code.

--Returns 1 if valid, 0 if invalid 
CREATE OR REPLACE FUNCTION valid_npi (npi in varchar)    
RETURN number
IS  
    tot NUMBER := 24; --80840 prefix for npi
    val NUMBER := 0;
BEGIN  
  if( 
    npi is null or 
    length(npi) <> 10 or 
    LENGTH(TRIM(TRANSLATE(npi, '0123456789', ' ')))>0 --not numeric
  ) then
    return 0;
  end if;
  
  for i IN reverse 1 .. LENGTH(npi) loop
    val:=SUBSTR(npi, i, 1);
    if mod(i,2)=1 then
      val := val * 2;      
      if(val > 9) then
        val:=val-9;
      end if;
    end if;
    
    tot := tot + val;    
  end loop;

  if(MOD(tot, 10)=0) then
    return 1;
  else
    return 0;
  end if;  
END valid_npi;
  • http://ducquoc.wordpress.com DucQuoc.wordpress.com

    Thank you for sharing this. I was looking for an PL/SQL version :-) .
    (I have it implemented in Java & Ruby but a PL/SQL may be faster sometimes)