Regular expressions allow more powerful comparisons than the more standard LIKE
and NOT LIKE. Regular expression comparisons are a unique feature of
POSTGRESQL. They are very common in UNIX, such as in the UNIX
Table shows the regular expression operators
Regular expression operators
regular expression - case insensitive
not equal regular expression
not equal regular expression, case insensitive
table shows the regular expression
Regular expression special characters
any single character
set of characters
set of characters not equal
range of characters
range of characters not equal
zero or one of previous character
zero or multiple of previous characters
one or multiple of previous characters
Note that the caret (^ ) has a different meaning outside and inside square
brackets ([ ]). While regular expressions are powerful, they are
complex to create. Table shows some examples.
Regular expression examples
begins with D
D in second position
begins with D and contains e
begins with D, contains e, and then f
contains A, B, C, or D
~'[A-D]' or ~'[ABCD]'
contains A or a
~*'a' or ~'[Aa]'
not contains D
not begins with D
!~'^D' or ~'^[^D]'
begins with D, with one optional leading space
begins with D , with optional leading spaces
begins with D, with at least one leading space
Figure shows examples of queries using
regular expressions. See the comment above each query for descriptions.
Regular expression sample queries
Figure shows two more complex
Complex regular expression
The first query shows the way to properly test for a trailing n. Because
char() columns have trailing space to fill the column, you need to test
for possible trailing spaces. See section for complete
coverage on character data types. The second query may be surprising. Some think
it returns rows that do not contain an S. Instead, the query returns
all rows that have any character that is not an S. Sandy
contains characters that are not S, such as a, n, d, and y,
so that row is returned. The test would only prevent rows containing only S's
from being printed.
Because regular expressions have a powerful special character command set, creating
them can be difficult. Try some queries on the friends table until you
are comfortable with regular expression comparisons.