Using Like operator in Parameterized DB Query Reader

Hi ,

I find Parameterized DB Query Reader , working good , except for when I want to use Like operator with my values from my columnlist in the where condition. Appreciate help on resolution of using like operator in my query for ex:
SELECT * FROM #table# AS "table" where "description" like %$Desc$%

I need to read the description values from input file and convert into input columnlist to the Parameterized DB Query Reader.

I have validated using DBQuery Reader, but I have custom write the query parsing the list of variable values ( used column to variable node) , which is not scalable solution for me .

Regards,
Nivedita

You have a list of LIKE conditions you want to check individually? Have you already wrapped it into a loop?
If you want to modify the variable you could also use string manipulation variable before using it inside the where statement
br

Hi @Daniel_Weikert , I have used string manipulation variable to include “%”. No, I have not wrapped it in loop., I was looking at Parameterized DB Query Reader support for like operator

Regards,
Nivedita

Hi @nivedita_dixit , you should expand on “working good , except for when I want to use Like operator with my values from my columnlist in the where condition”.

What’s the issue? Does it execute, but you don’t get the expected results? Does it not execute and you get an error? What’s the error?

You’re not giving much to go about.

In any case, I can guess that Knime is complaining about %$Desc$%. That’s an invalid value.

There are 2 ways to fix this. Depending on what DB system you are working with (another information that you could have provided), this first option should normally work:
Option 1: Use CONCAT() in your statement to append wildcard:
SELECT * FROM #table# AS "table" where "description" like CONCAT('%', $Desc$, '%')

The CONCAT() would execute on the DB side, that is why it would depend on what DB system you are using.

If this does not work, then you can use the next option.
Option 2: Append the wildcards via Knime before using the column
You can use the String Manipulation node to do this:
join("%", $Desc$, "%")

And your statement should then simply do:
SELECT * FROM #table# AS "table" where "description" like $Desc$

Obviously if you want to keep the original values of Desc, you can save the manipulation to a new column, let’s say Desc_with_wildcard, and then your statement can read off that new column:
SELECT * FROM #table# AS "table" where "description" like $Desc_with_wildcard$

These should solve your issue.

1 Like

Thanks @bruno29a . Works fine with the option 1 suggested by you . Appreciate your inputs .
Had tried with option2 earlier, but have few issues.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.