Blogs Home
Wednesday, May 23, 2018 12:00 PM

SQL Tip: Checking for Invalid Provider NPI Numbers

Written by Thomas Harlan, Jim McGrath; Reporting Services Team - iatricSystems



Most, if not all of us involved in US healthcare are familiar with the National Provider Identifier (NPI), which is a 10-digit number starting with a 1 or a 2. Each NPI uniquely identifies a specific provider. The provider can be a person or organization. NPIs are used and embedded in billing and claims workflows.

What you may not have noticed, however, is that NPIs have an internal structure, and the last digit is a check digit. This means we can test whether each provider in our MEDITECH system has a properly formatted NPI by executing the Luhn algorithm against the first 9 characters and then comparing to the 10th.

The SQL code for a function to execute the reverse walk-through of the number and do the calculation is interesting, since it’s a useful technique to use in other situations.

But right now, we want to know if we have any doctors with a mangled NPI in our provider dictionary. And once you’ve installed this shiny new function in your zcus database, you can do that very easily:

MEDITECH 6.0 and up:




Provider_Number_MEDITECH_CSNow wait just a minute…

Doesn’t the MIS Provider Dictionary check this already, when you enter an NPI? After all, there are routines in NPR to do this same calculation…

Provider_Number_MIS_DictionaryWhich is the case, except that some testing at a variety of sites shows you can still plug an invalid NPI into the provider fields. So, it’s not a bad idea to verify that any docs with a mismatched NPI is supposed to be that way (typically these are provider records set up for testing workflows).

Extra Credit

Are you worried that calling a function with a WHILE loop inside of it may be too slow? You could also perform the same check with a tally-table (go to the bottom of the article) style approach, which can get you much faster processing. However, the tally-table is a little harder to understand, if you haven’t worked with that methodology before.

If you need more help…

Our Report Writing team can help you fix reports, create new ones, make old ones run faster, and much more. Simply reach out to your Iatric Systems Account Executive, or our NPR report writing team at, or click the button below to contact us and learn how we can help support your team!