Movatterモバイル変換

expr NOT REGEXPpat,expr NOT RLIKEpat

This is the same asNOT (expr REGEXPpat).

expr REGEXPpat,expr RLIKEpat

Returns 1 if the stringexpr matches the regular expression specified by the patternpat, 0 otherwise. If eitherexpr orpat isNULL, the return value isNULL.

RLIKE is a synonym forREGEXP.

The pattern can be an extended regular expression, the syntax for which is discussed inRegular Expression Syntax. The pattern need not be a literal string. For example, it can be specified as a string expression or table column.

Regular expression operations use the character set and collation of the string expression and pattern 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 inSection 10.8.4, “Collation Coercibility in Expressions”. If either argument is a binary string, the arguments are handled in case-sensitive fashion as binary strings.

mysql> SELECT 'Michael!' REGEXP '.*';+------------------------+| 'Michael!' REGEXP '.*' |+------------------------+|                      1 |+------------------------+mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';+---------------------------------------+| 'new*\n*line' REGEXP 'new\\*.\\*line' |+---------------------------------------+|                                     0 |+---------------------------------------+mysql> SELECT 'a' REGEXP '^[a-d]';+---------------------+| 'a' REGEXP '^[a-d]' |+---------------------+|                   1 |+---------------------+

^

Match the beginning of a string.

mysql> SELECT 'fo\nfo' REGEXP '^fo$';                   -> 0mysql> SELECT 'fofo' REGEXP '^fo';                      -> 1

$

Match the end of a string.

mysql> SELECT 'fo\no' REGEXP '^fo\no$';                 -> 1mysql> SELECT 'fo\no' REGEXP '^fo$';                    -> 0

.

Match any character (including carriage return and newline).

mysql> SELECT 'fofo' REGEXP '^f.*$';                    -> 1mysql> SELECT 'fo\r\nfo' REGEXP '^f.*$';                -> 1

a*

Match any sequence of zero or morea characters.

mysql> SELECT 'Ban' REGEXP '^Ba*n';                     -> 1mysql> SELECT 'Baaan' REGEXP '^Ba*n';                   -> 1mysql> SELECT 'Bn' REGEXP '^Ba*n';                      -> 1

a+

Match any sequence of one or morea characters.

mysql> SELECT 'Ban' REGEXP '^Ba+n';                     -> 1mysql> SELECT 'Bn' REGEXP '^Ba+n';                      -> 0

a?

Match either zero or onea character.

mysql> SELECT 'Bn' REGEXP '^Ba?n';                      -> 1mysql> SELECT 'Ban' REGEXP '^Ba?n';                     -> 1mysql> SELECT 'Baan' REGEXP '^Ba?n';                    -> 0

de|abc

Alternation; match either of the sequencesde orabc.

mysql> SELECT 'pi' REGEXP 'pi|apa';                     -> 1mysql> SELECT 'axe' REGEXP 'pi|apa';                    -> 0mysql> SELECT 'apa' REGEXP 'pi|apa';                    -> 1mysql> SELECT 'apa' REGEXP '^(pi|apa)$';                -> 1mysql> SELECT 'pi' REGEXP '^(pi|apa)$';                 -> 1mysql> SELECT 'pix' REGEXP '^(pi|apa)$';                -> 0

(abc)*

Match zero or more instances of the sequenceabc.

mysql> SELECT 'pi' REGEXP '^(pi)*$';                    -> 1mysql> SELECT 'pip' REGEXP '^(pi)*$';                   -> 0mysql> SELECT 'pipi' REGEXP '^(pi)*$';                  -> 1

{1},{2,3}

Repetition;{n} and{m,n} notation provide a more general way of writing regular expressions that match many occurrences of the previous atom (or“piece”) of the pattern.m andn are integers.

To be more precise,a{n} matches exactlyn instances ofa.a{n,} matchesn or more instances ofa.a{m,n} matchesm throughn instances ofa, inclusive. If bothm andn are given,m must be less than or equal ton.

m andn must be in the range from0 toRE_DUP_MAX (default 255), inclusive.

mysql> SELECT 'abcde' REGEXP 'a[bcd]{2}e';              -> 0mysql> SELECT 'abcde' REGEXP 'a[bcd]{3}e';              -> 1mysql> SELECT 'abcde' REGEXP 'a[bcd]{1,10}e';           -> 1

[a-dX],[^a-dX]

Matches any character that is (or is not, if^ is used) eithera,b,c,d orX. A- character between two other characters forms a range that matches all characters from the first character to the second. For example,[0-9] matches any decimal digit. To include a literal] character, it must immediately follow the opening bracket[. To include a literal- character, it must be written first or last. Any character that does not have a defined special meaning inside a[] pair matches only itself.

mysql> SELECT 'aXbc' REGEXP '[a-dXYZ]';                 -> 1mysql> SELECT 'aXbc' REGEXP '^[a-dXYZ]$';               -> 0mysql> SELECT 'aXbc' REGEXP '^[a-dXYZ]+$';              -> 1mysql> SELECT 'aXbc' REGEXP '^[^a-dXYZ]+$';             -> 0mysql> SELECT 'gheis' REGEXP '^[^a-dXYZ]+$';            -> 1mysql> SELECT 'gheisa' REGEXP '^[^a-dXYZ]+$';           -> 0

[.characters.]

Within a bracket expression (written using[ and]), matches the sequence of characters of that collating element.characters is either a single character or a character name likenewline. The following table lists the permissible character names.

The following table shows the permissible character names and the characters that they match. For characters given as numeric values, the values are represented in octal.

mysql> SELECT '~' REGEXP '[[.~.]]';                     -> 1mysql> SELECT '~' REGEXP '[[.tilde.]]';                 -> 1

[=character_class=]

Within a bracket expression (written using[ and]),[=character_class=] represents an equivalence class. It matches all characters with the same collation value, including itself. For example, ifo and(+) are the members of an equivalence class,[[=o=]],[[=(+)=]], and[o(+)] are all synonymous. An equivalence class may not be used as an endpoint of a range.

[:character_class:]

Within a bracket expression (written using[ and]),[:character_class:] represents a character class that matches all characters belonging to that class. The following table lists the standard class names. These names stand for the character classes defined in thectype(3) manual page. A particular locale may provide other class names. A character class may not be used as an endpoint of a range.

mysql> SELECT 'justalnums' REGEXP '[[:alnum:]]+';       -> 1mysql> SELECT '!!' REGEXP '[[:alnum:]]+';               -> 0

[[:<:]],[[:>:]]

These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in thealnum class or an underscore (_).

mysql> SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]';   -> 1mysql> SELECT 'a xword a' REGEXP '[[:<:]]word[[:>:]]';  -> 0