Regular expressions for MySQL

1. mysql's regular expression only makes a subset of the SQL language, which can match basic characters, strings.
For example: SELECT * from wp_posts where post_name REGEXP ' Hello ' to retrieve all rows containing hello in the dequeue post_name
REGEXP '. og '. is a special character in a regular expression. It means matching one character, so dog,hog,mog and so on can match.

Attention:
About the difference between like and regexp: like matches the entire column. If the matched text appears only in the column values, like does not find it, and the corresponding row does not return (except, of course, wildcards). While regexp matches within the column values, if the matching matched text appears in the column values, RegExp will find it, and the corresponding row is returned, a very important difference (of course, if the positional symbols ^ and $ are adapted, you can implement the regexp to match the entire column rather than a subset of the columns).
About case sensitivity: MySQL expression matching (from version 3.23.4) is case insensitive. If you want to distinguish case, you should use the binary keyword, such as where post_name REGEXP binary ' Hello. 000 '

2. About or matches
In order to search for one of the two strings, use |, for example, select * FROM Products where pro_id REGEXP ' 1000|2000 ' ORDER by pro_id;
This allows both 1000 and 2000 to match and return, of course, using multiple | To match multiple strings

3. Just match a few characters []
For example, this will match [0123456789] can match 0 to 9,[1-4][4-9] is also a valid range. In addition, the range is not necessarily numeric, [A-z] matches any alphabetic character
such as: SELECT Prod_name from Products WHERE prod_name REGEXP ' [1-5] Ton ' ORDER by Prod_name;

4. Match Special characters using \ to escape
\ \. able to match.
\\f page Change
\\n Line Break
\\r Enter
\\t watchmaking
\ \ Longitudinal Tabulation

Note: In order to match \ itself, you need to use \\\

5. Matching character classes
[: alnum:] Any letter and number (pass [a-za-z0-9])
[: Alpha:] Any character (same as [a-za-z])
[: Blank:] Spaces and tabs (same as [\\t])
[:d igit:] Any number (same as [0-9])
[: Lower:] Any lowercase letter
[: Upper:] Any uppercase letter
[: space:] Any white space character, including spaces

6. Match multiple instances, about repeating meta characters
* 0 or more matches
+ 1 or more matches (equals {1,})
？ 1 or more matches (equals {0,1})
{n} specified number of matches
{n,} not less than a specified number of matches
{N,m} matches the number of ranges (m not exceeding 255)

Example: Select Prod_name from the products where Prod_name REGEXP ' [[:D igit:]]{4} ', as described earlier, [:d Igit:] matches any koumine, thus it is a geometry for the number. {4} exactly requires that the preceding character (any number) appear 3 this, so [[:d igit:]]{4} matches any 4-digit number connected together, of course, the above example can also be written regexp ' [0-9][0-9][0-9][0-9] '

7. Locator
^ Beginning of the text
& End of text
[[: <:]] The beginning of the word
[[:]: The end of the word
By using these locators, you can make regexp like

From:http://www.cnblogs.com/fuyunbiyi/archive/2012/03/29/2423489.html

Regular expressions for MySQL