SSIS How to get part of a string by separator using Derived Column

3
votes

I trying to write an SSIS expression to get the right part of a string before the separator and then put the new string in a new column. I have used Substring and Findstring in derived column but I am having trouble.

Company
Virgin Altantic - F30008 
Google - F38115

I need to retrieve F30008 and F38115.

I used the expression:

(DT_STR,65,1252)(FINDSTRING(Company,"- ",1) != 0 ? (SUBSTRING(Company,1,FINDSTRING(Company,"-",1) + 2)) : Company)

From this expression I got:

Company
Virgin Altantic - 
Google -
3
ssis
Generally speaking, you will want to also post what you have tried (findstring and substring yes but specifically what did you use) and what was the precise error message you received. – billinkc

3 Answers

5
votes

Data:

|                    COMPANY |
|----------------------------|
|   Virgin Altantic - F30008 |
|            Google - F38115 |
| Google youtube - F38115169 |

Derived Column Code:

NewColumn1

SUBSTRING(Company,1,LEN(Company) - FINDSTRING(REVERSE(Company),"-",1) - 1)

NewColumn2

SUBSTRING(Company,LEN(Company) - FINDSTRING(REVERSE(Company),"-",1) + 3,FINDSTRING(REVERSE(Company),"-",1) - 2)

Result:

enter image description here

3
votes

Just use SSIS TOKEN with "-" delimiter and then use TRIM

2
votes

The problem you have is that you want the end of the string and your current code is giving you the beginning of the string.

The documentation for SUBSTRING specifies 3 parameters: expression, starting position and length. FINDSTRING is returning the position of the separator. What you are doing is specifying the start position at 1, the first character in your column.

Instead, make that the position of the separator, plus 2 spaces to account for the separator itself and the space. The length is going to be the LEN([Company]) - FINDSTRING([Company],"- ",1)

In all version of SSIS, we also have RIGHT which is a specialized version of SUBSTRING that you could have also used.