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](@NPI varchar(20))
RETURNS int AS
-- Returns 1 for valid or missing NPI
-- Returns 0 for invalid NPI
-- SELECT [dbo].[IsValidNPI]('1234567893')
-- SELECT [dbo].[IsValidNPI]('123456789a')
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