Geeks With Blogs

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:

1. Double the value of alternate digits beginning with the rightmost digit.
2. Add the individual digits of the products resulting from step 1 to the unaffected digits from the original number.
3. 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

Related Posts on Geeks With Blogs Matching Categories

Comments on this post: Validating NPI (National Provider Identifier) numbers in SQL

# re: Validating NPI (National Provider Identifier) numbers in SQL How do i get the 10 digit number from NPI?
Left by joseph couston on Feb 07, 2012 1:17 PM

# re: Validating NPI (National Provider Identifier) numbers in SQL Very nice function! Just one question: For NPI numbers, it is mentioned "the first digit being a 1 or a 2". Is there a reason this function doesn't exclude anything that starts with something other than 1 or 2?
Left by Kris on Dec 13, 2012 4:11 PM

# re: Validating NPI (National Provider Identifier) numbers in SQL why are we adding a constant 24 to the sum obtained any specific reason for that.
Left by Shobhit on Jun 18, 2014 4:55 AM

# re: Validating NPI (National Provider Identifier) numbers in SQL @Shobhit: it's constant that replaces 80840, which is kind of an "assumed" prefix indicating it's a healthcare provider in the US.

Bill's code simply uses the constant instead of including the 80840
Left by Jules on Oct 03, 2014 7:13 AM

# re: Validating NPI (National Provider Identifier) numbers in SQL why would you conclude missing as 1 since your fuction is called isValidNPI

missing is not considered as 'Valid'

just a strange thought :)
Left by Ray Ray on Jul 24, 2015 10:58 AM

# re: Validating NPI (National Provider Identifier) numbers in SQL Using a while loop in a scalar valued function is pretty inefficient. Below is an iTVF version that doesn't require any looping (My test on 1M row table containing NPI numbers has the iTVF version ~12X faster...

CREATE FUNCTION dbo.tfn_IsValidNPI
/* =======================================================================================================================
05/25/2017 JL, Created... This is an iTVF version of the function found at the following url:
http://geekswithblogs.net/bosuch/archive/2012/01/16/validating-npi-national-provider-identifier-numbers-in-sql.aspx
======================================================================================================================== */
(
@NPI VARCHAR(20)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH
cte_SplitNPI AS (
SELECT
NPI = 1
FROM
( VALUES (
SUBSTRING(@NPI, 1, 1), SUBSTRING(@NPI, 2, 1), SUBSTRING(@NPI, 3, 1), SUBSTRING(@NPI, 4, 1), SUBSTRING(@NPI, 5, 1),
SUBSTRING(@NPI, 6, 1), SUBSTRING(@NPI, 7, 1), SUBSTRING(@NPI, 8, 1), SUBSTRING(@NPI, 9, 1), SUBSTRING(@NPI, 10, 1)
)
) p (p1, p2, p3, p4, p5, p6, p7, p8, p9, p10)
CROSS APPLY ( VALUES (
RIGHT(CONCAT('0', CAST(p.p1 * 2 AS VARCHAR(2))), 2),
RIGHT(CONCAT('0', CAST(p.p3 * 2 AS VARCHAR(2))), 2),
RIGHT(CONCAT('0', CAST(p.p5 * 2 AS VARCHAR(2))), 2),
RIGHT(CONCAT('0', CAST(p.p7 * 2 AS VARCHAR(2))), 2),
RIGHT(CONCAT('0', CAST(p.p9 * 2 AS VARCHAR(2))), 2)
)
) d (d1, d3, d5, d7, d9)
CROSS APPLY ( VALUES (
LEFT(d.d1, 1), RIGHT(d.d1, 1),
LEFT(d.d3, 1), RIGHT(d.d3, 1),
LEFT(d.d5, 1), RIGHT(d.d5, 1),
LEFT(d.d7, 1), RIGHT(d.d7, 1),
LEFT(d.d9, 1), RIGHT(d.d9, 1)
)
) s (s1l, s1r, s3l, s3r, s5l, s5r, s7l, s7r, s9l, s9r)
CROSS APPLY ( VALUES (24 + s.s1l + s.s1r + p.p2 + s.s3l + s.s3r + p.p4 + s.s5l + s.s5r + p.p6 + s.s7l + s.s7r + p.p8 + s.s9l +s.s9r) ) sv (SummedVal)
CROSS APPLY ( VALUES ((CEILING(sv.SummedVal / 10.0) * 10)) ) c (CeilingVal)
WHERE
LEN(@NPI) = 10
AND @NPI NOT LIKE '%[^0-9]%'
AND p.p10 = c.CeilingVal - sv.SummedVal
)

SELECT IsValidNPI = ISNULL((SELECT 1 FROM cte_SplitNPI sn), 0);
GO
Left by Jason Long on May 25, 2017 12:45 PM

# re: Validating NPI (National Provider Identifier) numbers in SQL In my previous comment omitted the 1st character check. Here is the correction.
`ALTER FUNCTION dbo.tfn_IsValidNPI/* =======================================================================================================================05/25/2017 JL, Created... This is an iTVF version of the function found at the following url: http://geekswithblogs.net/bosuch/archive/2012/01/16/validating-npi-national-provider-identifier-numbers-in-sql.aspx======================================================================================================================== */( @NPI VARCHAR(20))RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH cte_SplitNPI AS ( SELECT NPI = 1 FROM ( VALUES ( SUBSTRING(@NPI, 1, 1), SUBSTRING(@NPI, 2, 1), SUBSTRING(@NPI, 3, 1), SUBSTRING(@NPI, 4, 1), SUBSTRING(@NPI, 5, 1), SUBSTRING(@NPI, 6, 1), SUBSTRING(@NPI, 7, 1), SUBSTRING(@NPI, 8, 1), SUBSTRING(@NPI, 9, 1), SUBSTRING(@NPI, 10, 1) ) ) p (p1, p2, p3, p4, p5, p6, p7, p8, p9, p10) CROSS APPLY ( VALUES ( RIGHT(CONCAT('0', CAST(p.p1 * 2 AS VARCHAR(2))), 2), RIGHT(CONCAT('0', CAST(p.p3 * 2 AS VARCHAR(2))), 2), RIGHT(CONCAT('0', CAST(p.p5 * 2 AS VARCHAR(2))), 2), RIGHT(CONCAT('0', CAST(p.p7 * 2 AS VARCHAR(2))), 2), RIGHT(CONCAT('0', CAST(p.p9 * 2 AS VARCHAR(2))), 2) ) ) d (d1, d3, d5, d7, d9) CROSS APPLY ( VALUES ( LEFT(d.d1, 1), RIGHT(d.d1, 1), LEFT(d.d3, 1), RIGHT(d.d3, 1), LEFT(d.d5, 1), RIGHT(d.d5, 1), LEFT(d.d7, 1), RIGHT(d.d7, 1), LEFT(d.d9, 1), RIGHT(d.d9, 1) ) ) s (s1l, s1r, s3l, s3r, s5l, s5r, s7l, s7r, s9l, s9r) CROSS APPLY ( VALUES (24 + s.s1l + s.s1r + p.p2 + s.s3l + s.s3r + p.p4 + s.s5l + s.s5r + p.p6 + s.s7l + s.s7r + p.p8 + s.s9l +s.s9r) ) sv (SummedVal) CROSS APPLY ( VALUES ((CEILING(sv.SummedVal / 10.0) * 10)) ) c (CeilingVal) WHERE LEN(@NPI) = 10 AND @NPI NOT LIKE '%[^0-9]%' AND p.p1 IN (1,2) AND p.p10 = c.CeilingVal - sv.SummedVal ) SELECT IsValidNPI = ISNULL((SELECT 1 FROM cte_SplitNPI sn), 0);GO`
Left by Jason Long on May 25, 2017 1:23 PM