How to use SQL LIKE condition with multiple values in PostgreSQL?

Question

Is there any shorter way to look for multiple matches:

 SELECT * 
 from table 
 WHERE column LIKE "AAA%" 
    OR column LIKE "BBB%" 
    OR column LIKE "CCC%"

This questions applies to PostgreSQL 9.1, but if there is a generic solution it would be even better.

It reads well, works well and is easy to understand. Why swap that for alternatives like the popular answer and get into knots when you need to find pipes (`|`) or brackets in actual data? — RichardTheKiwi, Oct 18 '12 at 16:07
@RichardTheKiwi Because is it not as easy to dynamically build. See my answer on how to avoid dealing with a regex. — Clodoaldo Neto, Oct 19 '12 at 12:25
@Clo It's still code that's not ISO standard and *harder* to understand by comparison. Except possibly `any (values('AAA%'), ('BBB%'), ('CCC%')` That one looks good. — RichardTheKiwi, Oct 19 '12 at 12:30

score 79 · Accepted Answer · answered Oct 18 '12 at 15:20

79

Perhaps using SIMILAR TO would work ?

SELECT * from table WHERE column SIMILAR TO '(AAA|BBB|CCC)%';

answered Oct 18 '12 at 15:20

tozka

3,211
19
23

1

+1 `SIMILAR TO` would work, but for the example in the question it should be `SIMILAR TO 'AAA%|(BBB|CCC)%'` or `SIMILAR TO '(AA%|BBB%|CCC%'` – GarethD Oct 18 '12 at 15:39
`Similar to` was added to Postgres 7.3, so Netezza systems and the like may not allow its use. – Joseph Coco Feb 27 '16 at 03:00
Very elegant solution – Toren Mar 13 '22 at 22:06

score 41 · Answer 2 · edited May 23 '17 at 12:26

41

Use LIKE ANY(ARRAY['AAA%', 'BBB%', 'CCC%']) as per this cool trick @maniek showed earlier today.

edited May 23 '17 at 12:26

Community

1
1

answered Oct 18 '12 at 22:10

Craig Ringer

307,061
76
688
778

You just missed the syntax. The list must be array or set. – Clodoaldo Neto Oct 19 '12 at 12:21
I believe `ANY` was added in Postgres 7.4. – Joseph Coco Feb 27 '16 at 03:03

Clodoaldo Neto · Answer 3 · 2012-10-19T12:20:11.213

33

Using array or set comparisons:

create table t (str text);
insert into t values ('AAA'), ('BBB'), ('DDD999YYY'), ('DDD099YYY');

select str from t
where str like any ('{"AAA%", "BBB%", "CCC%"}');

select str from t
where str like any (values('AAA%'), ('BBB%'), ('CCC%'));

It is also possible to do an AND which would not be easy with a regex if it were to match any order:

select str from t
where str like all ('{"%999%", "DDD%"}');

select str from t
where str like all (values('%999%'), ('DDD%'));

edited Oct 19 '12 at 12:20

answered Oct 19 '12 at 12:14

Clodoaldo Neto

118,695
26
233
260

1

I believe `ANY` and `ALL` were added in Postgres 7.4. – Joseph Coco Feb 27 '16 at 03:04

score 13 · Answer 4 · answered Jun 19 '19 at 21:38

13

You can use regular expression operator (~), separated by (|) as described in Pattern Matching

select column_a from table where column_a ~* 'aaa|bbb|ccc'

answered Jun 19 '19 at 21:38

Diego

151
1
6

score 6 · Answer 5 · answered Oct 09 '19 at 13:48

6

Following query helped me. Instead of using LIKE, you can use ~*.

select id, name from hosts where name ~* 'julia|lena|jack';

answered Oct 09 '19 at 13:48

Anto

3,128
1
20
20

score -5 · Answer 6 · answered Aug 06 '18 at 18:31

-5

You might be able to use IN, if you don't actually need wildcards.

SELECT * from table WHERE column IN ('AAA', 'BBB', 'CCC')

answered Aug 06 '18 at 18:31

ACT

1

How to use SQL LIKE condition with multiple values in PostgreSQL?

6 Answers6

Linked