Using the MySQL regular expression __mysql must be known

Source: Internet
Author: User
Tags control characters

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.