SQL to Take Last Name, First Name and MI and just return First and Last Name

Question

I have searched Stack and getting close to what I need, but can't seem to figure out why when I have a person with just Last Name, First Name that the first name ends up as the Middle Initial? I am only needing the Last Name and First name to display it as First and Last name.

Sample Data:

EMP_ID	EMP_NAME
1234	JONES, JAMES R
5687	SMITH, BILL

What I want to end up with is:

EMP_ID	EMP_NAME_FULL
1234	JAMES JONES
5687	BILL SMITH

I am working with this code and once I can figure out how to resolve getting the first name to work, I planned to combine the First and Last Name substring/Parsing to one name.

SELECT DISTINCT
 EMP_ID
,EMP_NAME
,SUBSTRING(EMP_NAME, 1, CHARINDEX(',', EMP_NAME) - 1) AS LASTNAME
,CASE WHEN PARSENAME(REPLACE(EMP_NAME, ',', '.'),1) LIKE '% %' THEN PARSENAME(REPLACE(PARSENAME(REPLACE(EMP_NAME, ',', '.'),1), ' ', '.'),2) ELSE PARSENAME(REPLACE(EMP_NAME, ',', '.'),1) END FIRSTNAME
,CASE WHEN PARSENAME(REPLACE(EMP_NAME, ' ', '.'),1) LIKE '%,%' THEN NULL ELSE PARSENAME(REPLACE(EMP_NAME, ' ', '.'),1) END MI

FROM EMP_TABLE

Gisela · Answer

Try this:

[MYSQL]


   select emp_name, 
          SUBSTRING_INDEX(SUBSTRING_INDEX(emp_name, ', ', -1), ' ', 1),
          SUBSTRING_INDEX(emp_name, ',', 1)
     from EMP_TABLE;

[MYSQL SSMS]


   select emp_name, 
          CASE 
                WHEN CHARINDEX(' ', SUBSTRING(emp_name, CHARINDEX(', ',emp_name)+2)) >0 
                    THEN SUBSTRING(SUBSTRING(SUBSTRING(emp_name, CHARINDEX(', ',emp_name)+2), ' '),1,CHARINDEX(' ',SUBSTRING(emp_name, CHARINDEX(', ',emp_name)+2)))
                ELSE SUBSTRING(SUBSTRING(emp_name, CHARINDEX(', ',emp_name)+2), ' ')
          END AS firstname,
          SUBSTRING(emp_name, 1, CHARINDEX(',',emp_name) - 1) AS lastname
     from EMP_TABLE;

I couldn't try it with MYSQL SSMS but the function CHARINDEX is the same as INSTR, the only difference is in INSTR you specify first where to look and then what to look. I try it like this with your data and it worked. Then, I convert every INSTR into CHARINDEX, and I inverted the parameters.

[DEMO]



   with tmp as
   (
   select 'JONES, JAMES R' as emp_name from dual
   union all
   select 'SMITH, BILL' as emp_name from dual
   )
   select emp_name, 
          CASE 
            WHEN INSTR(SUBSTRING(emp_name, INSTR(emp_name,', ')+2),' ') >0 
              THEN SUBSTRING(SUBSTRING(SUBSTRING(emp_name, INSTR(emp_name,', ')+2), ' '),1,INSTR(SUBSTRING(emp_name, INSTR(emp_name,', ')+2),' '))
            ELSE 
              SUBSTRING(SUBSTRING(emp_name, INSTR(emp_name,', ')+2), ' ')
          END AS firstname,
          SUBSTRING(emp_name, 1, INSTR(emp_name,',') - 1) AS lastname
   from tmp;

SQL to Take Last Name, First Name and MI and just return First and Last Name

1 Answers