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 '%.%'
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
```