Following will return TRUE since both strings do not match with case sensitivity enabled regex=# SELECT 'similar' !~ 'Similar' as result Similarly, in order to achieve the opposite result, we just prefix ~ operator with ‘!’ sign so we switch to case insensitive operator and re-run the same query regex=# SELECT 'similar' ~* 'Similar' as result following will fail as the pattern matching is case sensitive regex=# SELECT 'similar' ~ 'Similar' as result Here are some examples: regex=# SELECT 'similar' ~ 'similar' as result So how do we use them ? The most common use of this operator is to select rows based on whether a column matches a regular expression. !~* (Does not match regular expression, case insensitive).!~ (Does not match regular expression, case sensitive).~* (Matches regular expression, case insensitive).~ (Matches regular expression, case sensitive).
The tilde operator returns true or false depending on whether or not a regular expression can match a string or a part thereof. PostgreSQL supports following four operators for POSIX regular expression matching (also known as the tilde operator). For starters, I will discuss POSIX-style regular expressions and see some basic uses.
MYSQL REGULAR EXPRESSION NOT THERE SERIES
This post is first in the series of blogs I plan to write on the RE topic with respect to PostgreSQL. POSIX regular expressions provide a more powerful means for pattern matching than the LIKE and SIMILAR TO operators. There are some more advanced techniques for advanced pattern matching requirements but those will very likely involve writing some user defined functions using Perl or Tcl. POSIX-style regular expressions (BREs and EREs).PostgreSQL’s regular expressions supports three separate approaches to pattern matching: PostgreSQL 9 Cookbook – Chinese EditionĪ regular expression is a special text string used to describe a search pattern.
PostgreSQL Server Programming Cookbook – 2nd Edition.PostgreSQL 9 Administration Cookbook – 3rd Edition.PostgreSQL High Availability Cookbook – 2nd Edition.