PostgreSQL wildcard LIKE for any of a list of words

by

PostgreSQL also supports full POSIX regular expressions:

select * from table where value ~* 'foo|bar|baz';

The ~* is for a case insensitive match, ~ is case sensitive.

Another option is to use ANY:

select * from table where value  like any (array['%foo%', '%bar%', '%baz%']);
select * from table where value ilike any (array['%foo%', '%bar%', '%baz%']);

You can use ANY with any operator that yields a boolean. I suspect that the regex options would be quicker but ANY is a useful tool to have in your toolbox.

More Related Contents:

  1. Select rows which are not present in other table
  2. Optimize GROUP BY query to retrieve latest row per user
  3. How to implement a many-to-many relationship in PostgreSQL?
  4. Aggregate columns with additional (distinct) filters
  5. Computed / calculated / virtual / derived columns in PostgreSQL
  6. Check if value exists in Postgres array
  7. Insert data in 3 tables at a time using Postgres
  8. How to use ANY instead of IN in a WHERE clause?
  9. Is there a shortcut for SELECT * FROM?
  10. sql statement error: “column .. does not exist”
  11. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  12. sql ORDER BY multiple values in specific order?
  13. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  14. PostgreSQL: Which Datatype should be used for Currency?
  15. Sorting null values after all others, except special
  16. Reference alias in WHERE clause
  17. NOT NULL constraint over a set of columns
  18. How do I alter the position of a column in a PostgreSQL database table?
  19. Discard millisecond part from timestamp
  20. How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?
  21. Alphanumeric sorting with PostgreSQL
  22. Transposing an sql result so that one column goes onto multiple columns
  23. SQL column reference “id” is ambiguous
  24. How to use multiple CTEs in a single SQL query?
  25. IN Clause with NULL or IS NULL
  26. postgres – where in (list) – column does not exist
  27. How to get a list column names and datatypes of a table in PostgreSQL?
  28. Postgresql query between date ranges
  29. Why isn’t row level security enabled for Postgres views?
  30. Is there a postgres CLOSEST operator?

Leave a Comment