Is it possible to perform a “LIKE” statement in a

Question

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?

Answer 1

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.

Answer 2

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