Use ilike any() with escape character

user_1512047 0
Viewed 544

In PostgreSQL you can do a case insensitive query with ILIKE:

select * from test where value ilike 'half is 50$%' escape '$'

And you can query multiple values at once by combining ILIKE with ANY()

select * from test where value ilike any(array['half is 50%', 'fifth is 20%'])

The query above will match 'Fifth is 2019', which I do not want, but when I try to use ILIKE and ANY() with an escape character I get a syntax error.

Am I missing something stupid, or is this simply not supported? If not, is there another way to query in a case insensitive way with multiple values at once?

EDIT: To clarify, the query will accept parameters through JDBC, so the actual SQL will look something like

select * from test where value ilike any(?) escape '$'

This is why I'm looking make % and _ from the user input be interpreted as literals.

jdbc postgresql
2 Answers

The ESCAPE clause in ILIKE refers only to literals and does not apply to expressions. You should use a backslash, or if not possible, you can try:

with test(value) as (
values 
    ('half is 50%'),
    ('half is 50x'),
    ('fifth is 20%'),
    ('fifth is 2000')
)

select * 
from test
where value ilike any(select replace(unnest(array['half is 50$%', 'fifth is 20$%']), '$', '\'))

    value     
--------------
 half is 50%
 fifth is 20%
(2 rows)

Looks a bit clumsy but works well.

user_19957380

To match them as raw strings, you may use the ~* operator for insensitive match.

knayak=# select 'Half is 50%' ~* any(array['half is 50%', 'fifth is 20%'])
knayak-# ;
 ?column?
----------
 t            --True
(1 row)

knayak=# select 'fifth is 20' ~* any(array['half is 50%', 'fifth is 20%']);
 ?column?
----------
 f             --False
(1 row)

If you wish to escape the right hand operands of ilike use "escape" string constants, which are an extension to the SQL standard. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote

knayak=# select 'Half is 50%' ilike any(array[E'half is 50\\%', E'half is 20\\%'])
knayak-# ;
 ?column?
----------
 t
(1 row)

DEMO

user_79985910
Related