title: SQL- Get the substring after first space and second space in separate columns
body: <p>I have a column as <code>FullName</code> containg <code>FirstName</code>, <code>MiddleName</code>, <code>LastName</code> in it.<br>
  For example:</p>
  
  <blockquote>
    <p>FullName: Marilyn Kean Kirkland</p>
  </blockquote>
  
  <p>I want to have 3 separate columns for <code>FirstName</code>, <code>MiddleName</code> and <code>LastName</code> from the <code>FullName</code> by taking a substring from it.</p>
  
  <p>I am pulling the <code>FirstName</code> by using the code:</p>
  
  <pre><code>substring(c.LegalName, 1, CHARINDEX(' ', c.LegalName)) as 'First Name'
  </code></pre>
  
  <p>I am wondering how can I pull just the middle name which comes after first space and before second space?<br>
  Also, I want to pull the last name which comes after the second space?</p>