0

I have a JDBC query that performs an ILIKE operation.

select project_name from project_table where project_name ILIKE '%test%';

The above returns all the results matching (wildcarded) the term test.

However, when the project_name is %project_name and the user searches for just %, the query returns all the rows instead of just the matching row.

Essentially the query becomes select project_name from project_table where project_name ILIKE '%%%';

I understand the concept of wildcarding in postgresql, but is there a way to just limit the result matching the %?

1
  • Please remember to always disclose the Postgres version in use. Commented Jan 7, 2022 at 2:33

1 Answer 1

3

Escape the special meaning with a backslash (\):

WHERE project_name ILIKE '%\%project_name';

Matches any string that ends with '%project_name'.
The first % serves as wildcard, the second, escaped \% matches a literal '%'.

Or use the function I provided here (along with detailed explanation) :

If '%\%%' works like '%%%' for you then - like jjanes hinted - you are probably running with the outdated setting standard_conforming_strings = off, where \ is interpreted away as escape character.
The default is on since Postgres 9.1. (!)

Solution: Double up \ and prepend the string with E, like: E'%\\%%'.

db<>fiddle here

Or, much rather, upgrade to a current version of Postgres with proper settings. See:

7
  • ILIKE '%\%project_name'; works when the search-term is %project_name. However, when the search-term is just %, I still get back all the results (WHERE project_name ILIKE '%\%%';) instead of just one row -- %project_name. Commented Jan 6, 2022 at 15:32
  • @Pavan: That shouldn't be (unless all project names contain a literal %). db<>fiddle here. Maybe some component of your food-chain escapes the `\`? Log statements and check your database log what Postgres actually receives if you are unsure. Commented Jan 6, 2022 at 15:47
  • Executing the query directly in psql: select project_name from project_table WHERE project_name ILIKE '%\%%'; project_name -------------- %test_name project-1 project-1 (3 rows) Commented Jan 6, 2022 at 16:31
  • 1
    @Pavan - if you wish to demonstrate SQL functionality, far better to use db<>fiddle (dbfiddle.uk) and put the link here - comments don't format very well and your message from the above isn't very clear - a fiddle would rectify that!
    – Vérace
    Commented Jan 6, 2022 at 16:34
  • 2
    @Pavan Maybe you have standard_conforming_strings = off.
    – jjanes
    Commented Jan 6, 2022 at 22:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.