Parsing Name to get Middle Initial

[kandukurip]

PLEASE HELP ME OUT.

I WANT TO PARSE THE PATIENT_NAME TO GET FIRSTNAME, LASTNAME AND MIDDLE INITIAL OUT OF IT.

EX PATIENT_NAME: MORRIS, BERTHA E

LASTNAME: MORRIS
FIRSTNAME: BERTHA
MIDDLE INITIAL: E

I AM USING SQL SERVER TO DO THAT.

select substring('MORRIS, BERTHA E',1,charindex(',','MORRIS, BERTHA E')-1)

select substring('MORRIS, BERTHA E',charindex(',','MORRIS, BERTHA E')+2,charindex(' ','MORRIS, BERTHA E')-2)

SO, I AM GETTING FIRST NAME AND LASTNAME, NOT MIDDLE INITIAL.
ALL NAMES DO NOT HAVE MIDDLE INITIALS.


THANKS IN ADVANCE
JOE

 

[jdegeorge]

kandukurip

Check out this post from Bob Scriver. It's about parsing an address field, but the concept is the same.

thread701-526037

"Get it right the first time, that's the main thing..."