title: Extracting part of name from string
body: <p>I was trying to extract the lastName, firstName, middleName from Name column which contains whole name format.</p>
  
  <p>here's the sample names which I'm trying to extract:</p>
  
  <pre><code>DELA CRUZ, JUAN PONCE SR. ENRILE
  ATIENZA, ROBERTO JR. SANTO
  GONZA, MARK ANTHONY, DELLY-LO
  </code></pre>
  
  <p>I want to extract them like these:</p>
  
  <pre><code>    LastName    | FirstName       | MiddleName
      DELA CRUZ   | JUAN PONCE SR.  | ENRILE
      ATIENZA     | ROBERTO JR.     | SANTOS
      GONZA       | MARK ANTHONY    | DELLY
  </code></pre>
  
  <p>This is my script so far</p>
  
  <pre><code>DECLARE @Name    VARCHAR(50) = 'DELA CRUZ, JUAN PONCE SR. ENRILE'
  
  SELECT
  SUBSTRING(@Name, 0, CharIndex(',', @Name)) LastName,
  REVERSE(LEFT(REVERSE(@Name), CharIndex(' ', reverse(@Name))-1)) MiddleName
  </code></pre>
  
  <p>I also having a problem when extracting middle with -LO suffix.</p>
  
  <p>thank you</p>