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