If you work with any kind of healthcare data, then odds are you’ll deal with NPI (National Provider Identifier) numbers. The NPI is a 10-digit number consisting of 9 digits (with the first digit being a 1 or a 2) followed by a check digit. The check digit uses the Luhn algorithm, which is calculated like this:
- Double the value of alternate digits beginning with the rightmost digit.
- Add the individual digits of the products resulting from step 1 to the unaffected digits from the original number.
- Subtract the total obtained in step 2 from the next higher number ending in zero. This is the check digit. If the total obtained in step 2 is a number ending in zero, the check digit is zero.
So for example:
Let's say the 9-digit part of the NPI is 123456789. NPI without check digit: 1 2 3 4 5 6 7 8 9 Step 1: Double the value of alternate digits, beginning with the rightmost digit. 2 6 10 14 18 Step 2: Add constant 24, plus the individual digits of products of doubling, plus unaffected digits. 24 + 2 + 2 + 6 + 4 + 1 + 0 + 6 + 1 + 4 + 8 + 1 + 8 = 67 Step 3: Subtract from next higher number ending in zero. 70 – 67 = 3 Check digit = 3 NPI with check digit = 1234567893
We needed a way to check our SQL database for valid NPI numbers, so this SQL does the trick:
CREATE FUNCTION [dbo].\IsValidNPI) RETURNS int AS
-- Returns 1 for valid or missing NPI -- Returns 0 for invalid NPI
-- SELECT [dbo].\IsValidNPI
-- SELECT [dbo].\IsValidNPI\
BEGIN Declare @Result int, @Len int, @Index int, @Total int, @TmpStr varchar(2), @TmpInt int Set @Result = 0 Set @Total = 0 Set @NPI = IsNull(@NPI,'') Set @Len = Len(@NPI)
If @Len = 0 Set @Result = 1 Else Begin If @Len <> 10 or IsNumeric(@NPI) = 0 or @NPI like '%.%' or (@NPI not like '1%' and @NPI not like '2%') Set @Result = 0 Else Begin Set @Index = @Len
While @Index > 1 Begin Set @TmpStr = Substring(@NPI, @Index, 1) Set @Total = @Total + Cast(@TmpStr as int)
Set @TmpStr = SubString(@NPI, @Index-1, 1) Set @TmpInt = Cast(@TmpStr as int) * 2 If @TmpInt < 10 Set @Total = @Total + @TmpInt Else Begin Set @TmpStr = Cast(@TmpInt as varchar(2)) Set @Total = @Total + Cast(Substring(@TmpStr,2,1) as int) Set @Total = @Total + Cast(Substring(@TmpStr,1,1) as int) End Set @Index = @Index - 2 End
If @Len % 2 = 1 Set @Total = @Total + Cast(Substring(@NPI,1,1) as int) If @Len = 10 Set @Total = @Total + 24
If @Total % 10 = 0 Set @Result = 1 Else Set @Result = 0 End End Return(@Result) END