SSIS How to get part of a string by separator usin

问题:

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 - 

回答1:

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:

回答2:

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.

回答3:

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