17

I'm using a Derived Column Task to change column data using a CASE WHEN statement. However, I need to be able to say..

SQL CODE WOULD BE:

CASE WHEN Column01 LIKE '%i%' THEN '0' ELSE '1' END


In SSIS Expression Language that would be:

[Column01] == "i" ? "0" : "1"  (that's for equals i, not, LIKE %i%.


Is it possible to use a LIKE operator?

iamtheratio
  • 569
  • 4
  • 9
  • 16

2 Answers2

29

I know it is an old question, but these days I found a good answer on web.

If you want a expression for Contains like '%value%' you could use : 

FINDSTRING(col, "value", 1) > 0`

If you want a expression for Start with like 'value%' you could use :

FINDSTRING(col, "value", 1) == 1

And finally, if you want a expression for End with like '%value' you could use :

REVERSE(LEFT(REVERSE(col), X))  == "value"

More details look this useful resource : Basic SSIS Equivalents to T-SQL's LIKE

SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
Guilherme de Jesus Santos
  • 4,955
  • 5
  • 43
  • 68
14

I believe you'll want to use the FINDSTRING function.

FINDSTRING(character_expression, searchstring, occurrence)

...

FINDSTRING returns null if either character_expression or searchstring are null.

Conspicuous Compiler
  • 6,403
  • 1
  • 40
  • 52
  • Thanks! I was able to write this using what you gave me, hopefully the results will be correct. ISNULL([Column]) ? "1" : ! ISNULL(FINDSTRING([Column],"I",1)) ? "0" : "1" - If column 1 is null, then 1.. THEN if column 1 has an I in there it will return a value, therefore not null, so it will then show a 0, else 1. – iamtheratio Jan 19 '11 at 18:56