title: SQL server separating first name, last name, and middle initial permanently
body: <p>I have a table that has first name, last name, and middle initial, when there is one, in one column like this:</p>
  
  <pre><code>Name
  Doe, John B
  Smith, Bob
  </code></pre>
  
  <p>Some names do not have a middle initial and I just want to get rid of the ones that do. So far I have this query:</p>
  
  <pre><code>SELECT   [table].[Name]
      ,LEFT([table].[Name], CHARINDEX(',', [table].[Name]) - 1) AS [Surname]
      ,REPLACE(SUBSTRING([table].[Name], CHARINDEX(',', [table].[Name]), LEN([table].[Name])), ',', '') AS [FirstName]
  FROM    [table]
  </code></pre>
  
  <p>Which gives me this result:</p>
  
  <pre><code>Surname  First Name
  Doe      John B
  Smith    Bob
  </code></pre>
  
  <p>Is there something that I can add to just get rid of the middle initial when it occurs? Also, can I permanently change this data so it remains in two separate columns?</p>