asked on

TSQL TO PARSE NAMES

I want to parse out FIRST, MIDDLE, and LAST names using TSQL.

The problem I have is that data names are separated by space only.

select SUBSTRING(TBANAME, 1, CHARINDEX(' ', TBANAME) - 1) AS [Last_Name] from MYTABLE .... but not sure how to get the rest....

Here is sample of data field name [TBANAME]:

[TBANAME]
ARROYO JOEL E
ENGLANOFF JOSEPH SHIMON
CRAWFORD SHANNON DAVID
FASSIHI AMIR ALI
CASTILLO FRANCISCO ALVIN
GUIRGUIS SYLVIA MARY E
HATTI VIKRAM MYSORE
KAM CHRISTINE NORRED

Lee

There is no easy way to do this. Your data contains situations where there are 4 parts to a name.

GUIRGUIS SYLVIA MARY E

You'll also struggle with names like:

Jean-Claude Van Damme

As the last name is a two part name and you cannot determine in code which names are like this.

You could get first and last names and assume everything else is the middle. For example:

select left(tbaname, CHARINDEX(' ', tbaname) - 1) AS [First_Name],
       REPLACE(replace(tbaname, left(tbaname, CHARINDEX(' ', tbaname)), ''), reverse(left(reverse(tbaname), CHARINDEX(' ', reverse(tbaname)))), '') as [middle_name],
       reverse(left(reverse(tbaname), CHARINDEX(' ', reverse(tbaname)) - 1)) AS [Last_Name]
 from mytable

Open in new window

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick Matthews

My code above assume that, in the case of GUIRGUIS SYLVIA MARY E, that:

LName: GUIRGUIS
FName: SYLVIA
MName: MARY E

It also works with, say GUIRGUIS

LName: GUIRGUIS
FName: <zero length string>
MName: <zero length string>

GUIRGUIS SYLVIA:

LName: GUIRGUIS
FName: SYLVIA
MName: <zero length string>

If TBANAME is null, then all three name parts come back null.

epicazo

ASKER

Thanks so much.

The TSQL you provided worked great, but I had to modify it to extract only first letter of MIDDLE name because it works better for the data I have...

SELECT CASE WHEN LTRIM(RTRIM(SUBSTRING(TBANAME2,LEN(TBANAME2)-3,4))) IN ('MD','DO','M.D.','D.O.') then 'DR' else '' end as [nam_pfx_cd]
,LEFT(TBANAME, CHARINDEX(' ', TBANAME + ' ') - 1) AS [Last_Name],RTRIM(SUBSTRING(TBANAME, CHARINDEX(' ', TBANAME + ' ') + 1, CHARINDEX(' ', TBANAME + ' ', CHARINDEX(' ', TBANAME + ' ') + 1) - CHARINDEX(' ', TBANAME + ' '))) AS [First_Name]
,SUBSTRING(TBANAME, CHARINDEX(' ', TBANAME + ' ', CHARINDEX(' ', TBANAME + ' ') + 1) + 1, LEN(TBANAME)) AS Mname, SUBSTRING(TBANAME, CHARINDEX(' ', TBANAME + ' ', CHARINDEX(' ', TBANAME + ' ') + 1) + 1,1) as [Middle_Name]
from MYTABLE