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

Need Your Help

Illegal reflective access by org.springframework.cglib.core.ReflectUtils$1

java spring spring-boot java-9 java-module

My JDK 9+181 Spring Boot 2.0.0.BUILD-SNAPSHOT CLI application displays this warning on startup:

What is the max size of VARCHAR2 in PL/SQL and SQL?

sql oracle plsql varchar2

I am on Oracle 10g. In a requirement I need to increase the size of a pl/sql VARCHAR2 variable. It is already at 4000 size. I have read that