Search code examples
sql-serverssisexpressionetl

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

Solution

  • of course you can:

    enter image description here

    just configure your derived columns like this:

    enter image description here

    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