SQL- Get the substring after first space and second space in separate columns

Question

I have a column as FullName containg FirstName, MiddleName, LastName in it.
For example:

FullName: Marilyn Kean Kirkland

I want to have 3 separate columns for FirstName, MiddleName and LastName from the FullName by taking a substring from it.

I am pulling the FirstName by using the code:

substring(c.LegalName, 1, CHARINDEX(' ', c.LegalName)) as 'First Name'

I am wondering how can I pull just the middle name which comes after first space and before second space?
Also, I want to pull the last name which comes after the second space?

Answer 1

SQL Server doesn't have very good string manipulation functions. This is easier with subqueries:

select firstname,
       stuff(reverse(stuff(reverse(legalname), 1, len(lastname) + 1, '')),
             1, len(firstname) + 1, '')
from (select legalname,
             left(legalname, charindex(' ', legalname) - 1) as firstname,
             right(legalname, charindex(' ', reverse(legalname)) - 1) as lastname
      . . .
     ) c

However, I would be very careful, because not all people have three part names. And others have suffixes (JR, SR) and other complications.