CODEWITHSUNDEEP
sql-servert-sqlssisetlssis-2014
k brake
k brake

Reputation: 21

Parse Full Name into separate column Name fields

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.

Upvotes: 2

Views: 699

Answers (1)

Eric Brandt
Eric Brandt

Reputation: 8101

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:

enter image description here

Upvotes: 2

Related Questions