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
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
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
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."