Geeks With Blogs

Bill Osuch - Random geek notes

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

Posted on Monday, January 16, 2012 2:46 PM SQL | Back to top


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

# re: Validating NPI (National Provider Identifier) numbers in SQL
Requesting Gravatar...
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
Requesting Gravatar...
If you're asking how can you obtain an NPI number for yourself, if you are a healthcare provider you can go to: https://nppes.cms.hhs.gov/NPPES/Welcome.do
Left by Bill on Feb 07, 2012 2:03 PM

# re: Validating NPI (National Provider Identifier) numbers in SQL
Requesting Gravatar...
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
Requesting Gravatar...
You're right, I forgot to include the check for the first digit. I have updated the code. Thanks for spotting that!
Left by Bill on Dec 19, 2012 2:47 PM

# re: Validating NPI (National Provider Identifier) numbers in SQL
Requesting Gravatar...
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
Requesting Gravatar...
@Shobhit: it's constant that replaces 80840, which is kind of an "assumed" prefix indicating it's a healthcare provider in the US.

Please see https://www.cms.gov/Regulations-and-Guidance/HIPAA-Administrative-Simplification/NationalProvIdentStand/downloads/NPIcheckdigit.pdf, which explains it nicely.

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
Requesting Gravatar...
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
Requesting Gravatar...
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
Requesting Gravatar...
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

Your comment:
 (will show your gravatar)


Copyright © Bill Osuch | Powered by: GeeksWithBlogs.net