Oracle 5.0, Справочное руководство

Страница: 919 / 3232

Regular Expressions
899
+--------------------------------------------+
| STRCMP(@s1, @s3 COLLATE latin1_general_ci) |
+--------------------------------------------+
| 0 |
+--------------------------------------------+
12.5.2. Regular Expressions
Table 12.9. String Regular Expression Operators
Name Description
NOT REGEXP
[899] Negation of REGEXP
REGEXP
[899] Pattern matching using regular expressions
RLIKE
[899] Synonym for REGEXP
A regular expression is a powerful way of specifying a pattern for a complex search.
MySQL uses Henry Spencer's implementation of regular expressions, which is aimed at conformance
with POSIX 1003.2. MySQL uses the extended version to support pattern-matching operations
performed with the
REGEXP
[899] operator in SQL statements.
This section summarizes, with examples, the special characters and constructs that can be used in
MySQL for
REGEXP
[899] operations. It does not contain all the details that can be found in Henry
Spencer's
regex(7)
manual page. That manual page is included in MySQL source distributions, in
the
regex.7
file under the
regex
directory. See also Section 3.3.4.7, “Pattern Matching” .
•
expr NOT REGEXP pat
[899] ,
expr NOT RLIKE pat
[899]
This is the same as
NOT (expr REGEXP pat)
.
•
expr REGEXP pat
[899] ,
expr RLIKE pat
[899]
Performs a pattern match of a string expression
expr
against a pattern
pat
. The pattern can be
an extended regular expression. The syntax for regular expressions is discussed in Section 12.5.2,
“Regular Expressions” . Returns
1
if
expr
matches
pat
; otherwise it returns
0
. If either
expr
or
pat
is
NULL
, the result is
NULL
.
RLIKE
[899] is a synonym for
REGEXP
[899] , provided for
mSQL
compatibility.
The pattern need not be a literal string. For example, it can be specified as a string expression or
table column.
Note
Because MySQL uses the C escape syntax in strings (for example, “
\n
” to
represent the newline character), you must double any “
\
” that you use in
your
REGEXP
[899] strings.
REGEXP
[899] is not case sensitive, except when used with binary strings.
mysql> SELECT 'Monty!' REGEXP '.*';
-> 1
mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
-> 1
mysql> SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';
-> 1 0
mysql> SELECT 'a' REGEXP '^[a-d]';
-> 1
REGEXP
[899] and
RLIKE
[899] use the character set and collations of the arguments when
deciding the type of a character and performing the comparison. If the arguments have different
character sets or collations, coercibility rules apply as described in
Section 10.1.7.5, “Collation of
Expressions” .