Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Parsing a Name Field

Status
Not open for further replies.
Feb 6, 2003
48
US
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:
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top