Having Trouble Parsing a Name field 1

[RichardOneil]

Greetings -

I am pulling data from our HR system and need to split the name into separate pieces. Currently, the name field is in the following format: DOE,JOHN Q (there may or may not be a middle name/initial). I can get the last name and the first name/MI but I can't split the first name and middle initial into separate data fields. Here is my current code:

SELECT a.name AS NAME,
       a.number AS EENUM,
       b.status AS STATUS,
       b.dept AS DPTNO,
       c.name AS DPTNAME,
       b.jobcode AS JOBCODE,
       d.description AS JOBTITLE,
       LEFT(a.name, CHARINDEX(',', a.name)-1) AS LastName,
       SUBSTRING(a.name, CHARINDEX(',', a.name)+1,LEN(a.name)-CHARINDEX(',',a.name)) AS FirstName,
       
FROM LIVE_PPSHS..PPEmployeeFile a
LEFT JOIN LIVE_PPSHS..PPEmpPositionFields b ON a.employee=b.employee
LEFT JOIN LIVE_MIS..MisGlDept c ON b.dept=c.number
LEFT JOIN LIVE_PPSHS..PpJobCodeDictionary d ON b.jobcode=d.mnemonic
WHERE (b.status='ACTIVE' or b.status='LEAVE')

Thanks in advance for the assist.

 

[markros]

select EENUM, Status, DPTNo, DPTName, .., LastName,
LEFT(FirstName, CASE WHEN charindex('_',FirstName) = 0 THEN len(Firstname) ELSE charindex('_',FirstName)- 1 END) as FirstName, similar for the MiddleInitial.
from (SELECT a.name AS NAME,
a.number AS EENUM,
b.status AS STATUS,
b.dept AS DPTNO,
c.name AS DPTNAME,
b.jobcode AS JOBCODE,
d.description AS JOBTITLE,
LEFT(a.name, CHARINDEX(',', a.name)-1) AS LastName,
SUBSTRING(a.name, CHARINDEX(',', a.name)+1,LEN(a.name)-CHARINDEX(',',a.name)) AS FirstName,

FROM LIVE_PPSHS..PPEmployeeFile a
LEFT JOIN LIVE_PPSHS..PPEmpPositionFields b ON a.employee=b.employee
LEFT JOIN LIVE_MIS..MisGlDept c ON b.dept=c.number
LEFT JOIN LIVE_PPSHS..PpJobCodeDictionary d ON b.jobcode=d.mnemonic
WHERE (b.status='ACTIVE' or b.status='LEAVE')) Parsed

It would much better to have the fields separately in the beginning.

Also you may want to use Split function here to simplify the retrieval of the names.

 

[RichardOneil]

Thank you for that valuable post...I had a couple small bugs to work out, but you put me on the right track. Thanks so much.

 

[markros]

You're welcome - though I believe it would be easier with custom functions (or even table-level function) to properly parse the name.