Is it possible to perform a "LIKE" statement in a SSIS Expression?

iamtheratio picture iamtheratio · Jan 19, 2011 · Viewed 39.5k times · Source

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

Guilherme de Jesus Santos picture Guilherme de Jesus Santos · Sep 13, 2012

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