epicazo
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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(TBAN AME2,LEN(T BANAME2)-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(SUBSTRIN G(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
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(TBAN
,LEFT(TBANAME, CHARINDEX(' ', TBANAME + ' ') - 1) AS [Last_Name],RTRIM(SUBSTRIN
,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
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:
Open in new window