Menu
The PostgreSQL LIKE operator is used to match text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the LIKE operator will return true, which is 1. The percent sign represents zero, one, or multiple numbers or characters.
Use ILIKE : SELECT * FROM table WHERE columnName ILIKE 'R%'; or a case-insensitive regular expression: SELECT * FROM table WHERE columnName ~* '^R.
PostgreSQL wildcard is used to match text values from matching patterns. Like operator is used to find matching text values from the table.
To match a literal underscore or percent sign without matching other characters, the respective character in pattern must be preceded by the escape character. The default escape character is the backslash but a different one can be selected by using the ESCAPE clause.
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.
You can use Postgres' SIMILAR TO operator which supports alternations, i.e.
select * from table where lower(value) similar to '%(foo|bar|baz)%';
Actually there is an operator for that in PostgreSQL:
SELECT *
FROM table
WHERE lower(value) ~~ ANY('{%foo%,%bar%,%baz%}');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With