SSIS How to get part of a string by separator

I need an SSIS expression to get the left part of a string before the separator, and then put the new string in a new column. I checked in derived column, it seems no such expressions. Substring could only return string part with fixed length.

For example, with separator string - :

Art-Reading Should return Art Art-Writing Should return Art Science-chemistry Should return Science 

P.S. I knew this could be done in MySQL with SUBSTRING_INDEX(), but I'm looking for an equivalent in SSIS, or at least in SQL Server

4 Answers

Better late than never, but I wanted to do this too and found this.

TOKEN(character_expression, delimiter_string, occurrence) TOKEN("a little white dog"," ",2) 

returns little the source is below

of course you can:

just configure your derived columns like this:

Here is the expression to make your life easier:

SUBSTRING(name,1,FINDSTRING(name,"-",1) - 1) 

FYI, the second "1" means to get the first occurrence of the string "-"

EDIT: expression to deal with string without "-"

FINDSTRING(name,"-",1) != 0 ? (SUBSTRING(name,1,FINDSTRING(name,"-",1) - 1)) : name 

You can specify the length to copy in the SUBSTRING function and check for the location of the dash using CHARINDEX

SELECT SUBSTRING(@sString, 1, CHARINDEX('-',@sString) - 1) 

For the SSIS expression it is pretty much the same code:

SUBSTRING(@[User::String], 1, FINDSTRING(@[User::String], "-", 1)-1) 

if SUBSTRING length param returns -1 then it results in error, "The length -1 is not valid for function "SUBSTRING". The length parameter cannot be negative. Change the length parameter to zero or a positive value."