|The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
RRP £14.95 ($19.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 similar to
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 behavior where the pattern
can 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.
?denotes repetition of the previous item zero or one time.
}denotes repetition of the previous item exactly m times.
,}denotes repetition of the previous item m or more times.
}denotes repetition of the previous item at least m and not more than n times.
()can 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 the period (
.) 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 the entire data string, or 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.
Some examples, with
#" delimiting the return string:
substring('foobar' from '%#"o_b#"%' for '#') oob substring('foobar' from '#"o_b#"%' for '#') NULL
|ISBN 9781906966041||The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference||See the print edition|