RichardOneil
MIS
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:
Thanks in advance for the assist.
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:
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.