title: Extract last name, first name and suffix into separate columns
body: <p>I was wondering if someone could provide me an easy way to extract the names into different columns as below. There is a comma after the Last Name and space between First Name, Middle Initial, and Suffix. Greatly appreciate it.</p>
  
  <p><strong>Stored Data:</strong></p>
  
  <pre><code>Name
  Walker,James M JR
  Smith,Jack P
  Smith,Whitney
  </code></pre>
  
  <p><strong>Required result:</strong></p>
  
  <pre><code>LastName   FirstName   Suffix
  Walker     James       JR
  Smith      Jack
  Smith      Whitney
  </code></pre>
  
  <p><strong>Tried Code:</strong></p>
  
  <pre><code>select top 5 Name,
  LEFT(Name, CHARINDEX(',', Name) - 1) AS LastName,
  right(Name, len(Name) - CHARINDEX(',', Name)) as FirstName
  </code></pre>
  
  <p>Just having problem with separating First Name from Middle Initial and Suffix. Then getting Suffix from the last space from the right.</p>