Search code examples
sql-serverparsingsubstringcharindex

How to convert 'last name, first name' format to First Name Last Name in SQL Server

I am using MS SQL Server. I have a column, 'CUSTOMER_NAME'. Names are in Last Name, First Name format and some include suffix info such as 'Jr' or 'II'. I was using the below to split into two separate columns, one for first name and one for last name. The problem I run into is with the suffix info. It splits into both first and last name columns. Example 'Smith Jr, Joe' splits as 'Jr, Joe' in first_name and 'Smith Jr' as Last_Name. How can I modify this to correct the first_name output?

SELECT CUSTOMER_NAME,
       LEFT(CUSTOMER_NAME,CHARINDEX(', ',CUSTOMER_NAME + ' ')-1)  AS LAST_NAME,
       SUBSTRING(CUSTOMER_NAME,CHARINDEX(' ',CUSTOMER_NAME + ' ')+1,LEN(CUSTOMER_NAME)) AS FIRST_NAME
FROM   table_name
Solution

  • The modification below will cover both scenarios i.e. With and without suffix.

       SUBSTRING(CUSTOMER_NAME,CHARINDEX(', ',CUSTOMER_NAME + ' ')+2,LEN(CUSTOMER_NAME)) AS FIRST_NAME