16

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
Echo
  • 1,117
  • 4
  • 22
  • 43
  • possible duplicate of [Help with SubString in SSIS](http://stackoverflow.com/questions/5324590/help-with-substring-in-ssis) – Echo Jul 17 '13 at 13:47

4 Answers4

31

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

Aniket Kulkarni
  • 12,825
  • 9
  • 67
  • 90
David
  • 313
  • 3
  • 4
25

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
Diego
  • 34,802
  • 21
  • 91
  • 134
  • Thx a lot for the answer!! However, some values of the "name" column do not have a seperator and in this case, I wish the "new_name" column will be the same as "name". E.g. one of the name is "Science", and the new_name would just be "Science". So the whole process would be like this: if name has a seperator, then use the solution, else, copy from name to new_name – Echo Jun 07 '12 at 13:32
  • that can easily be solved with a conditional expression. See my edit! – Diego Jun 07 '12 at 13:40
9

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)
msmucker0527
  • 5,164
  • 2
  • 22
  • 36
2

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

Ashish
  • 29
  • 2