digitalBush

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;