Query for iLike list in PostgreSQL

Question

I'm writing an inefficient query as follows:

WHERE (Field iLIKE '%cat%' OR Field iLIKE '%dog%' OR Field  iLIKE '%animal%' OR Field  iLIKE '%pet%')

whereas what I would like to write is:

WHERE Field iLIKE ('%cat%','%dog%','%animal%','%pet%')

Is there an easy way to accomplish this?

Duplicate of http://stackoverflow.com/questions/4928054/postgresql-wildcard-like-for-any-of-a-list-of-words — Aaron Dietz, Jul 12 '16 at 19:42
I tried WHERE (DESCRIPTION similar to '%(cat|dog|animal)%') and it didn't work. Could you please advise? — ZJAY, Jul 12 '16 at 20:09

caiohamamura · Answer 1 · 2016-07-14T16:39:06.077

You can get the powerful regular expression operators ~ and ~* for case insensitive:

WHERE Field ~* '(cat|dog|animal|pet)'

SIMILAR TO is case sensitive, so you'd have to do:

WHERE Field SIMILAR TO '%([Cc][Aa][Tt]|[Dd][Oo][Gg]|[Aa][Nn][Ii][Mm][Aa][Ll]|[Pp][Ee][Tt])%'

ANY ARRAY will work too, but the perfomance is worse:

WHERE Field ILIKE ANY (array['%cat%','%dog%','%animal%','%pet%'])

With some dummy data with 1000000 rows with a BTREE INDEX in Field, I get these results:

╔═════════════╦═════════════╗
║  Operator   ║ Time (secs) ║
╠═════════════╬═════════════╣
║ ~*          ║ 1.5         ║
║ SIMILAR TO  ║ 1.4         ║
║ ANY ARRAY   ║ 4.0         ║
║ OR OR OR... ║ 4.0         ║
╚═════════════╩═════════════╝

score 3 · Answer 2 · answered Jul 12 '16 at 19:40

3

select 'cata' ilike any (array['%cat%','%dog%','%animal%','%pet%']);
 ?column? 
----------
 t

answered Jul 12 '16 at 19:40

Clodoaldo Neto

118,695
26
233
260

Query for iLike list in PostgreSQL

2 Answers2