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?
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;
~ operator instead of similar to for proper regexps, not SQL standard regexps.
select t.* from t where summary similar to '%(apple|banana|carrot|dog| . . .|zebra)%';
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))%')
