Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 751
Echo Avatar asked Jun 06 '12 20:06

Echo


People also ask

How do I split a string in SSIS?

In your SSIS Package, bring a Data Flow Task. Configure you Excel Source in Connection Manager and select the sheet you want the data from. Check in the Columns Tab to verify if the Columns are correct. Now drag the Derived Column Transformation and connect the Excel Source to it.

How do I split a string in SQL?

The STRING_SPLIT(string, separator) function in SQL Server splits the string in the first argument by the separator in the second argument. To split a sentence into words, specify the sentence as the first argument of the STRING_SPLIT() function and ' ' as the second argument. FROM STRING_SPLIT( 'An example sentence.

How do I use concatenate in SSIS Expression?

To concatenate two numeric values, both numeric values must be explicitly cast to a string data type. A concatenation can use only one BLOB data type: DT_TEXT, DT_NTEXT, or DT_IMAGE. If either element is null, the result is null. String literals must be enclosed in quotation marks.


Video Answer


2 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

http://technet.microsoft.com/en-us/library/hh213216.aspx

like image 142
David Avatar answered Sep 29 '22 15:09

David


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
like image 23
Diego Avatar answered Sep 29 '22 14:09

Diego