HOWTO use "LIKE" operator in Database Value Lookup or Database Join steps

[Notre1]

I'm try to do a lookup where one string should have many cases in which it is a substring of the other, but I can't figure out how to add the "%" wildcard character.

There is a like "Comparator" in the "Database Value Lookup", but the step errors out if I try '%Field1%' in the the lookup keys table. Is there a way to use the wildcard character to either of these steps?

[marabu]

I'm try to do a lookup where one string should have many cases in which it is a substring of the other, but I can't figure out how to add the "%" wildcard character.

Only one match is expected from a lookup, though. You can accept the first match (default) or signal an error on more than one match in the Database Value Lookup step.

There is a like "Comparator" in the "Database Value Lookup", but the step errors out if I try '%Field1%' in the the lookup keys table.

The LIKE expression must come from the input stream, not from the lookup table.

[hansva]

If both tables are on the same database you can solve this with a bad ass query or you can use one of following methods (there could be more but this is what comes to mind atm).

Depending on the dataset you can use a crazy solution like creating a cartesian product (warning this will probably be a lot of rows) and do the comparison in a regex, UDJC or javascript step.

Or a more performant way (depending on your table sizes) is to first use a database input step to select all your string you will use in the like statement and feed those to a parameterized query. (use a looping job for this).

like marabu stated all lookups steps are made to have only 1 match.

Greetz,
Hans