title: How to convert 'last name, first name' format to First Name Last Name in SQL Server
body: <p>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?</p>
  
  <pre><code>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
  </code></pre>