Postgresql: ILIKE query with % as search term

Question

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 %?

Answer 1

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) :

• Escape function for regular expression or LIKE patterns

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:

• Postgres 9.3 changes the standard_conforming_strings value
• Insert text with single quotes in PostgreSQL