|PostgreSQL Reference Manual - Volume 1 - SQL Language Reference|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 716 pages
RRP £32.00 ($49.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
7.7.2 SIMILAR TO Regular Expressions
string SIMILAR TO pattern [ESCAPE escape-character] string NOT SIMILAR TO pattern [ESCAPE escape-character]
SIMILAR TO operator returns true or
false depending on whether its pattern matches the given string.
It is much like
LIKE, except that it
interprets the pattern using the SQL standard's definition of a
regular expression. SQL regular expressions are a curious cross
LIKE notation and common regular
operator succeeds only if its pattern matches the entire string;
this is unlike common regular expression practice, wherein the pattern
may match any part of the string.
SIMILAR TO uses
% as wildcard characters denoting
any single character and any string, respectively (these are
.* in POSIX regular
In addition to these facilities borrowed from
SIMILAR TO supports these pattern-matching
metacharacters borrowed from POSIX regular expressions:
|denotes alternation (either of two alternatives).
*denotes repetition of the previous item zero or more times.
+denotes repetition of the previous item one or more times.
()may be used to group items into a single logical item.
A bracket expression
[...]specifies a character class, just as in POSIX regular expressions.
Notice that bounded repetition (
are not provided, though they exist in POSIX. Also, the dot (
is not a metacharacter.
LIKE, a backslash disables the special meaning
of any of these metacharacters; or a different escape character can
be specified with
'abc' SIMILAR TO 'abc' true 'abc' SIMILAR TO 'a' false 'abc' SIMILAR TO '%(b|d)%' true 'abc' SIMILAR TO '(b|c)%' false
substring function with three parameters,
extraction of a substring that matches an SQL
regular expression pattern. As with
SIMILAR TO, the
specified pattern must match to the entire data string, else the
function fails and returns null. To indicate the part of the
pattern that should be returned on success, the pattern must contain
two occurrences of the escape character followed by a double quote
"). The text matching the portion of the pattern
between these markers is returned.
substring('foobar' from '%#"o_b#"%' for '#') oob substring('foobar' from '#"o_b#"%' for '#') NULL
|ISBN 0954612027||PostgreSQL Reference Manual - Volume 1 - SQL Language Reference||See the print edition|