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;