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

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?

Cumin

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.

Resplendent

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. – Cumin

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

Convolute