CODEWITHSUNDEEP
sqlpostgresql
Huuuze
Huuuze

Reputation: 16357

Postgresql query to search text field for multiple strings

I have a table that contains a text field (summary). I recently received a request to query that field for any occurrences of a long list of strings. For example, I'd like to search the "summary" field for any occurrences of these strings:

Is there a simple query I can write that would return the IDs of the rows that contain any of these values?

Upvotes: 4

Views: 26374

Answers (3)

Tobia Zambon
Tobia Zambon

Reputation: 7629

The simplest way that comes in my mind is something like this:

SELECT "ID" FROM table WHERE "summary" IN ('apple', 'banana', 'carrot', 'dog', ....)

Try this out.

EDIT AFTER COMMENT: use the similar to operator:

select "ID" from table where upper("summary") similar to upper('%((apple)|(banana)|...|(zebra))%')

Upvotes: 8

Bohemian
Bohemian

Reputation: 425033

Use regex:

select * from mytable
where summary similar to '%(apple|banana|carrot|dog)%'

See SQLFiddle of this working

Upvotes: 7

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You could put them in one giant regular expression:

select t.*
from t
where summary similar to '%((apple)|(banana)|(carrot)|(dog)| . . .|(zebra))%';

An alternative, if you want more flexibility and care even less about performances:

with tosearch as (
      select '%apple%' as pattern union all
      . . .
      select '%zebra%'
     )
select t.*
from t join
     tosearch
     on t.summary like tosearch.pattern;

Upvotes: 12

Related Questions