? First/last names in SQL | Forum

Topic location: Forum home » Reporting » DR Reporting
Sep 8 '14
2014-09-08 11:35:05
I have to send a file that splits names in the provider dictionary into first, middle, and last names. Unfortunately our dictionary contains both people and institutions and even these have variations, eg:

1. SMITH,JOHN
2. SMITH,JOHN Q
Both 1 and 2 can be exactly as they are or with a suffix such as MD or DO, eg., SMITH,JOHN DO, but for some, it's like: SMITH,JOHN Q MD FCCP DABSM, but we would only want SMITH for last, JOHN for first, and Q as the middle.
We also have providers with names like these where it should make the entire name as the last name with nothing for the first or middle.
1. PEDIATRIC ASSOCIATES
2. IASA
3. FAMILY CENTER, LATROBE
What would be the cleanest way of doing this? Thus far I have incredibly messy CASE statements involving LEFT, RIGHT, and PATINDEX functions which thus hasn't addressed half the variations. If you already have SQL functions built that you can share, I'd appreciate it. Thus far I'm looking at 1) dumping all characters after the first name ie., just SMITH,JOHN and 2) names without commas "EMERSON SQUARE" where everything goes into the last name.
Thanks,
Ganesh Seshadri
Analyst
Indiana Regional Medical Center
(724) 357-7012
Sep 8 '14
2014-09-08 11:41:01
I would suggest writing a split function to parse the last and first name fields. First name fields then become problematic, but case functions can be used for the most common abbreviations such as MD, III, Jr., etc.
Organizations should use the Provider Type field to distinguish organizations from individuals. Have you looked at that field to see what your site has defined?

Thanks,
John Taylor
Systems Analyst, Information Technology
Augusta Health | Office: (540)332-4882
Sep 8 '14
2014-09-08 11:44:44
Ganesh,
I ran a report of all the names after I split them using SPLIT functions and some interesting spaghetti code. My report could retrieve about 95% of all names properly, then we had our HR department fix the employee names that did not fit. It was very similar to the provider dictionary - we just needed a standard. Once they were fixed on the source side (Meditech) the script could reliably pull them into columns for the interface.
Al


Sep 8 '14
2014-09-08 11:45:31
Your suggestion for provider type would certainly sound sensible now, but when the dictionary was set up 20+ years ago, this wasn't done. They're set up as "NS" for non-staff which also is used for a zillion actual physicians.
Ganesh Seshadri
Analyst
Indiana Regional Medical Center
(724) 357-7012
Sep 8 '14
2014-09-08 11:46:56
Garbage in, garbage out. Data quality issues are something IT can't resolve.

Thanks,
John Taylor
Systems Analyst, Information Technology
Augusta Health | Office: (540)332-4882