CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1
sorin
sorin

Reputation: 170846

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

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.

Upvotes: 85

Views: 254477

Answers (7)

Massimo Pallone
Massimo Pallone

Reputation: 11

if you want use not like to skip values use this query example:

select * from table 
where not field  like any(array['%aaa%','%bbb%','%ccc%','%ddd%'])

Upvotes: 1

Anto
Anto

Reputation: 3916

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

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

Upvotes: 6

Diego
Diego

Reputation: 191

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

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

Upvotes: 15

ACT
ACT

Reputation: 1

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

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

Upvotes: -6

Craig Ringer
Craig Ringer

Reputation: 325141

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

Upvotes: 50

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125544

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%'));

Upvotes: 36

tozka
tozka

Reputation: 3451

Perhaps using SIMILAR TO would work ?

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

Upvotes: 97

Related Questions