2

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
k brake
  • 21
  • 1
  • Welcome to SO K, you can find your solution already posted here at https://stackoverflow.com/questions/10921645/ssis-how-to-get-part-of-a-string-by-separator – N Subedi Dec 05 '18 at 18:23
  • And I think the relevant answer to the linked question uses the SSIS [TOKEN](https://learn.microsoft.com/en-us/sql/integration-services/expressions/token-ssis-expression?view=sql-server-2017) expression. It's not the accepted answer, but the one below it. – Eric Brandt Dec 05 '18 at 22:34
  • Possible duplicate of [SSIS How to get part of a string by separator](https://stackoverflow.com/questions/10921645/ssis-how-to-get-part-of-a-string-by-separator) – Zohar Peled Dec 06 '18 at 05:22

1 Answers1

2

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

Eric Brandt
  • 7,886
  • 3
  • 18
  • 35
  • Thanks I will try this. I had seen the others before I asked so I guess my confusion was all about dealing with the whole string. I'll have to make it conditional since some input rows will have two names with a comma and some will only have one name. – k brake Dec 06 '18 at 22:34