ILIKE, LIKE, etc giving me errors that I cannot seem to resolve

Queries and Resources
1

I am attempting to use ILIKE with wildcard matching. I have simplified the query to the following for troubleshooting purposes:

select * from table1 where column1 LIKE {{'%' + textinput6.value + '%' }};

I have tried the above with and without the wildcard matching. I have also tried ILIKE as well as LIKE (db is mysql). However, I keep getting the following error:

ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

2

For anyone experiencing similar problems, I solve the issue by writing the query as follows:

select * from table1 where column1 LIKE ('%{{ textinput6.value }}%');

3

nevermind. When I add the above statement to an and clause in a query that has multiple conditions, it fails again; giving me the same syntax error.

4

wait, it does work typo on my part. I left the semilcolon at the end.sorry

1 Like
5

Thanks for sharing! Yea, the LIKE and ILIKE can vary a lot across SQL dialects. Don’t even get me started about BigQuery…

6

ah great. That solved it - thanks for sharing.

7

Similar issue without the like. Just a simple query.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

 select column1, column2 from mytable WHERE column2 < {{ input1.value }}

the value is an integer as it should be. Why am I getting this error?

8

Hi @Greg_J Does the query work if you hardcode the value for {{ input1.value }}?

Does your resource have this setting checked on or off?

9

Hey @Tess. Yes, I checked this box after posting this and it worked, I just didn't come back and update the forum.
I will say, it's a bit tricky to get some of the MySQL Queries working in this manner, but seems to work nonetheless.

10

Hi @Greg_J! Glad that solved it! We typically recommend keeping it un-checked unless you really need to disable the setting (for security reasons). In cases where it solves issues with queries, we often recommend setting up another copy of a resource with that setting enabled to help limit the surface area that you have to keep in mind SQL injection for.

11