Полезная информация

next up previous contents index
Next: CASE Clause Up: Customizing Queries Previous: LIKE Comparison

Regular Expressions

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 grep command.6.5

Table [*] shows the regular expression operators and

Table: Regular expression operators
Comparison Operator
regular expression ~
regular expression - case insensitive ~*
not equal regular expression !~
not equal regular expression, case insensitive !~*

table [*] shows the regular expression special characters.
Table: Regular expression special characters
Test Special Characters
start ^
end $
any single character .
set of characters [ccc]
set of characters not equal [^ ccc]
range of characters [c-c]
range of characters not equal [^ c-c]
zero or one of previous character ?
zero or multiple of previous characters *
one or multiple of previous characters +
OR operator |

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.
Table: Regular expression examples
Test Operation
begins with D ~'^D'
contains D ~'D'
D in second position ~'^.D'
begins with D and contains e ~'^D.*e'
begins with D, contains e, and then f ~'D.*e.*f'
contains A, B, C, or D ~'[A-D]' or ~'[ABCD]'
contains A or a ~*'a' or ~'[Aa]'
not contains D !~'D'
not begins with D !~'^D' or ~'^[^D]'
begins with D, with one optional leading space ~'^  ?D'
begins with D , with optional leading spaces ~'^  *D'
begins with D, with at least one leading space ~'^  +D'

Figure [*] shows examples of queries using regular expressions. See the comment above each query for descriptions.
Figure: Regular expression sample queries
...vert Williamsport~~~\vert PA~~~\vert~22
\par (4~rows)\end{list}\par

Figure [*] shows two more complex regular expressions.

Figure: Complex regular expression queries
...vert Williamsport~~~\vert PA~~~\vert~22
\par (6~rows)\end{list}\par

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.

next up previous contents index
Next: CASE Clause Up: Customizing Queries Previous: LIKE Comparison
Bruce Momjian