Using the MySQL regular expression __mysql must be known

The regular expression acts as a matching party, comparing a pattern (regular expression) with a text string.

MySQL provides preliminary support for regular expressions with a WHERE clause, allowing you to specify that the data retrieved by select is filtered with a regular expression.

MySQL only supports a very small subset of the implementations of most regular expressions.

----------------------

9.2.1 Basic character Matching

The regexp is treated as a regular expression.

SELECT Prod_name
From Products
WHERE prod_name REGEXP ' 1000 '
ORDER by Prod_name;

------return------
+------------------------+
|    Prod_name    |
+------------------------+
|  JetPack  |
+------------------------+

. Represents a match for any one character.

SELECT Prod_name
From Products
WHERE prod_name REGEXP '. 000 '
ORDER by Prod_name;

------------return-----------

+-------------------------+
|     Prod_name    |
+-------------------------+
|   JetPack  |
|   JetPack  |
+-------------------------+

Regular expression matching in MySQL is case-insensitive.

For case sensitivity, the binary keyword can be used.

such as: where Prod_name REGEXP BINARY ' JetPack. 000 '

9.2.2 for or matching

To search for one of the two strings (or the string, or another string), use |.

| As an OR operator, represents one of the matches. More than two or conditions can be given.

SELECT Prod_name
From Products
WHERE prod_name REGEXP ' 1000 | 2000 '
ORDER by Prod_name;

------------return------------

+----------------------+
|  Prod_name   |
+----------------------+
| JetPack 1000 |
| JetPack 2000 |
+----------------------+

[] matches any single character.

[123] Defines a set of characters that means matching 1 or 2 or 3.

[] is another form of an or statement, [123] ton is the abbreviation for [1 | 2 | 3] ton.

^ Negates a character set and matches anything except the specified character. [^123] will match anything except these characters.

SELECT Prod_name
From Products
WHERE prod_name REGEXP ' [123] Ton '
ORDER by Prod_name;

-------------return------------

+--------------------+
| Prod_name   |
+--------------------+
| 1 Ton Anvil    |
| 2 Ton Anvil    |
+--------------------+

Match Range

[0123456789] or [0-9] will match the number 0 to 9

[A-z] matches any letter symbol

SELECT Prod_name
From Products
WHERE prod_name REGEXP ' [1-5] Ton '
ORDER by Prod_name;

----------return-----------

+-------------------+
|  Prod_name |
+-------------------+
|  . 5 Ton Anvil  |
|  1 Ton Anvil   |
|  2 Ton Anvil   |
+-------------------+

Match Special characters

\ \ is the leading. That is, escaping. All characters that have a special meaning within a regular expression must be escaped in this manner.

\\-means Find-

\\. Represents a lookup.

SELECT Prod_name
From vendors
WHERE vend_name REGEXP ' \ \. '
ORDER by Vend_name;

-------------return-------------

+----------------------+
|  Vend_name   |
+----------------------+
|  Furball INC    |
+----------------------+

\ \ is also used to refer to metacharacters (characters with special meaning)

\\f    Page Change
\\n    Line break
\\r    Enter
\\t    watchmaking
\\v    longitudinal watchmaking

Match character class

[: A;num:]    Any letters and numbers (same as [a-za-z0-9])
[: Alpha:]     Any character (same as [a-za-z])
[: Blank:]     Spaces and tabulation (same as [\\t])
[: Cntrl:]        ASCII control characters (ASCII 0 to 31 and 127)
[:d igit:]       Any number (same as [0-9])
[: Graph:]    Same as ["Print:], but not including spaces
[: Lower:]      Any lowercase line (same as [A-z])
[:p rint:]        any printable character
[:p UNCT:]      Neither [: Alnum:] Nor any of the characters in [: Cntrl:]
[Space:]       Any white space character, including spaces (same as [\\f\\n\\t\\r\\v])
[: Upper:]     Any size letter (same as [A-z])
[: Xdigit:]      arbitrary hexadecimal digits (same as [a-fa-f0-9])

Match multiple instances

Metacharacters              Description
*                       0 or more matches
+                      1 or more matches (equals {1,})
?                      0 or 1 matches (equals {0, 1})
{n}                    A specified number of matches
{N,}                  No less than a specified number of matches
{n, m}                range of matching numbers (m not more than 255)

The following example: S after? makes S optional, because it matches 0 or 1 occurrences of any character in front of it.

SELECT Prod_name
From Products
WHERE prod_name REGEXP ' \ \ ([0-9] sticks?\\) '
ORDER by Prod_name;

------------return------------

+-----------------------+
|  Prod_name    |
+-----------------------+
|  TNT (1 stick)   |
|  TNT (5 sticks) |
+-----------------------+

Match the 4-digit number that is connected at all time: where Prod_name REGEXP ' [[:d igit:]]{4} '

Locator characters

^         beginning of the text
        the end of the $ text
[[: <:]]  The beginning of the word
[[:;:]]  The end of the word

SELECT Prod_name
From Products
WHERE prod_name REGEXP ' ^[0-9\\.] '
ORDER by Prod_name;

-----------return----------

+---------------------+
|   Prod_name  |
+---------------------+
|  . 5 Ton Anvil   |
|  1 Ton Anvil    |
|  2 Ton Anvil    |
+---------------------+

^ 's dual Purpose: in the set (defined with []), it is used to negate the set. Otherwise, used to refer to the beginning of the string and.

Like matches the entire string, and RegExp matches the substring.

A simple regular expression test can test a regular expression with a select without using a database.

RegExp Check always returns 0 (no match) or 1 (match), can be measured with a text string of RegExp

Test the expressions and experiment with them. The corresponding syntax is as follows:

SELECT ' Hello ' REGEXP ' [0-9] '

This example returns 0 (because there is no number in the text hello).

From:http://www.cnblogs.com/way_testlife/archive/2010/09/17/1829567.html

Using the MySQL regular expression __mysql must be known