SSIS Transform -- Split one column into multiple columns

Question

I'm trying to find out how to split a column I have in a table and split it into three columns after the result is exported to a CSV file.

For example, I have a field called fullpatientname. It is listed in the following text format:

Smith, John C

The expectation is to have it in three separate columns:

Smith
John
C

I'm quite sure I have to split this in a derived column, but I'm not sure how to proceed with that

Answer 1

You are going to need to use a derived column for this process.

The SUBSTRING and FINDSTRING functions will be key to pull this off.

To get the first segment you would use something like this:

(DT_STR,25,1252) SUBSTRING([fullpatientname], 1, FINDSTRING(",",[fullpatientname],1)-1)

The above should display a substring starting with the beginning of the [fullpatientname] to the position prior to the comma (,).

The next segment would be from the position after the comma to the final space separator, and the final would be everything from the position following the final space separator to the end.