title: SQL Server : separate first and last name and remove middle initial into just two columns
body: <p>I have a column that has an individuals full name, including the middle initial. I am trying to separate the full name into just first name and last name and eliminating the middle name/initial. Some of the names in my database have a middle name/initial and some don't. The following query's are what I am using and they both do only half the trick.</p>
  
  <p>Query #1: returns the first name and middle name/initial in the 2nd column and eliminates the last name:</p>
  
  <pre><code>FirstName = LEFT(fullname, CHARINDEX(' ', fullname)), 
  LastName = RIGHT(fullname, CHARINDEX(' ', REVERSE(fullname)))
  </code></pre>
  
  <p>Query #2: returns the first name and combines the middle name/initial with the last name (with a space between the two):</p>
  
  <pre><code>FirstName = left(fullname,CHARINDEX(' ',fullname)),
  LastName =  SUBSTRING(fullname, CHARINDEX(' ',fullname)+1,LEN(fullname)-(CHARINDEX(' ',fullname)-1)) 
  </code></pre>