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