Ilike isn't working with the pattern matching condition

Queries and Resources
,
1

I've used ILIKE in my query.

select * from ABC where name ilike '{{name.value}}%'
  or id='{{id.value}}';

Here, if I run the query with the name its working fine. But, when I search with the id, it is exiting with 0 results and 504 error.
I also tried with {{name.value+'%'}}. But no luck.

Any help here please.

Thanks in advance!!

2

Depending on how you've set up your resource it may be that you're double quoting the search string.

eg ilike '{{name.value}}% is being translated into ''somename%''

I suspect you would want to remove the single quotes around your search query as the Retool engine will make that string safe from injection attacks and add the quotes for you

eg ilike {{ name.value +'%' }} might be all you need

You can confirm this by checking the prepared SQL statement in the editor window, it'll show you what query it's passing to your database

3

If you're using the retool PostgreSQL database to query, the correct syntax is:

where  "name" ilike {{name.value}} || '%'
1 Like
4

Thanks guys.
Actually, the issue is with the empty values. when i search with the ID, the name is looking for '%' value. Hence, it is resulting 0 rows and exiting with Error:504. I've used a case statement in the Resource query and that worked for me.

1 Like