SQL Tip - Parsing Patient and Provider Names

Tis but patient name tis my enemy;
Thou art five parts, though not in MEDITECH.
What is MEDITECH? It is nor first, nor last,
nor suffix, nor prefix, nor any other part
belonging to a name.
Oh! Be some other structure!
-- Jules Capulet, Report Developer, Verona University Healthcare

If you are lucky enough to be up on MEDITECH 6, you can find the patient name broken out into discrete components in this table:

Livefdb.dbo.HimRec_Main

Where the HIM module has helpfully separated out NameLast, NameFirst, NameMiddle, NamePrefix and NameSuffix. But if you are dealing with a provider name – your stars are crossed there – or if you’re on MAGIC or Client/Server – you also have to break down the formatted string into its constituent parts.

In very general terms, that is:

LastName, FirstName MiddleInitial

If your site has been scrupulous about following the convention for patient names. For provider names, however, all bets are off… there are often suffixes or sets of suffixes; additional slashes or semi-colons and additional information at either the front of the provider name or at the end.

In SQL there is no built-in "piece of" function, as you would find in NPR with the # operator. If we want to break up a string into pieces, we need to use either an assortment of functions:

SELECT LEFT(ADV.[Name],CHARINDEX(',',ADV.[Name])-1) AS LastName
RIGHT(ADV.Name,CHARINDEX(',',REVERSE(ADV.Name))-1) AS FirstName
FROM Livendb.dbo.AdmVisits ADV

But this example is too simple… our FirstName field also includes the Middle Initial, if any. Or anything trailing the middle initial.

Or we use a custom function like IatricPiece() to pick out the parts of the string that we want:

SELECT zcus.dbo.IatricPiece(',',1,'BERMAN,JOEL F MD') AS LastName
,zcus.dbo.IatricPiece(' ',1,zcus.dbo.IatricPiece(',',2,'BERMAN,JOEL F MD')) AS FirstName
,zcus.dbo.IatricPiece(' ',2,'BERMAN,JOEL F MD') AS MiddleName ;

This gets us:

Which works perfectly for names in the standard format. You could also do the same with a more elaborate nested use of CHARINDEX. But creating the IatricPiece() function in your database gives you an endlessly useful tool with little or no overhead.

But what about those thrice-damned Montagues? I mean, provider names? Ones that have wound up looking like this (due to dictionary maintenance):

** DO NOT USE ** SMITH,JOHN P JR NP/JONES

When you see that, sir, I dare say you will bite your thumb, sir! At me, even.

(Note! If you are on MT6, check your DMisProvider table… you might have LastName, FirstName, MiddleInitial already broken out for you!)

To tackle this we need a function of more parts – and more sophistication than the simple IatricPiece(). We need Jim McGrath’s IatricParseProviderName() which breaks a more complicated string down into a one-row table containing the following fields:

full_name
last_name
middle_name
prefix
suffix
degree
mail_name
associated_doctor
other

Which looks like this:

SELECT * FROM zcus.dbo.IatricParseProviderName('** DO NOT USE ** SMITH,JOHN P JR NP/JONES')

And produces:

The code for IatricParseProviderName() is attached to the tip for your edification, along with supporting code for IatricStripString() and IatricStripPuncuation() which help out along the way.

Once you have that in your zcus database, you can call the function via an OUTER APPLY (which acts like a SQL join, but is used to invoke a table-valued function once per row of your result set) and then use the name components in your SELECT field list:

SELECT DMP.ProviderID
,DMP.[Name] AS ProviderName
,DMP.Active AS ProviderActiveYN
,PPN.*
FROM Livendb.dbo.DMisProvider DMP
OUTER APPLY zcus.dbo.IatricParseProviderName(DMP.[Name]) AS PPN
;

But note that this only works if the code matches the name pattern in the dictionary…

(end)