Parse Full Name into separate column Name fields

Question

SSIS project SQL 2014 I have a full name string in a single column including commas as input and I need to parse last name, first name, middle, if they exist into separate columns for the output. Can this be in done in the select? I have seen solutions looking for specific parts of strings etc, but nothing that splits into 1 to 3 columns depending on the string in that particular row. For this integration, I can assume 1st position is last name, next is first if it exists and next is middle if it exists.

Answer 1

To sort of flesh out the comments, you can use a Derived Column transformation to generate your name parts from the full name. Any parts that don't exist will get blank spaces (not NULLS) in the output.

The syntax is TOKEN(character_expression, delimiter_string, occurrence)

Or, in your case:

LastName   | <add as new column> | TOKEN(FullName, ",", 1)
FirstName  | <add as new column> | TOKEN(FullName, ",", 2)
MiddleName | <add as new column> | TOKEN(FullName, ",", 1)

It should look something like this. It's a similar thing I did with table names: