Regular Expression (Regexp) in SQL)

Source: Internet
Author: User
Tags character classes control characters
In SQL query statements, regular expressions are sometimes required to specify the mode for their complex search. Some Regexp applications (not all) in MySQL statements are given below ):
1) ^
Match the start part of the string.
mysql> SELECT 'fo/nfo' REGEXP '^fo$';                   -> 0
mysql> SELECT 'fofo' REGEXP '^fo';                      -> 1

2) $

Matches the end part of the string.

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

3 ).

Match any character (including carriage return and new line ).

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

4) [: character_class:]

In a bracket expression (using [and]), [: character_class:] indicates the character class that matches all characters in the term class. The standard class name is:

Alnum

Character

Alpha

Character

Blank

White space characters

Cntrl

Control characters

Digit

Numeric characters

Graph

Graphical characters

Lower

Lowercase characters

Print

Graphical or space characters

Punct

Punctuation

Space

Space, tab, new line, and carriage return

Upper

Uppercase characters

Xdigit

Hexadecimal numeric characters

They represent character classes defined on the ctype (3) manual page. Other class names may be provided for specific regions. Character classes cannot be used as endpoints of a range.


Mysql>Select 'justnums' Regexp '[[: alnum:] + ';-> 1

Mysql>Select '!! 'Regexp' [[: alnum:] + ';-> 0

5) [[[: <:],
[[:>:]

These tags indicate word boundaries. They match the start and end of word respectively. Word is a series of character characters, with no character at the front and back. The character is a letter, digit, or underscore (_) in the alnum class (_).

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

To use a text instance with special characters in a regular expression, add two backslash (/) characters before it. The MySQL parser is responsible for interpreting one of them, and the regular expression library is responsible for interpreting the other. For example, to match the string "1 + 2" that contains the special character "+", in the following regular expression, only the last one is correct:

mysql> SELECT '1+2' REGEXP '1+2';                       -> 0
mysql> SELECT '1+2' REGEXP '1/+2';                      -> 0
mysql> SELECT '1+2' REGEXP '1//+2';                     -> 1

For other Regexp-related syntaxes, see the following table:
Character Meaning
/

In turn, that is, the characters after "/" are not interpreted as original meaning, such as/B/matching character "B ", when B is added to the front of the backslice bar // B/, it is converted to match the boundary of a word.
-Or-
Restores the function characters of a regular expression. For example, if "*" matches the previous metacharacters 0 or multiple times,/a */matches a, AA, AAA, after "/" is added,/a/*/will only match "*".

^ Matches the beginning of an input or a line,/^ A/matches "an A", but does not match "an"
$ Matches the end of an input or line,/a $/matches "an A", but does not match "an"
* Match the previous metacharacters 0 or multiple times./Ba */matches B, Ba, Baa, baaa
+ Match the previous metacharacters once or multiple times./Ba */matches Ba, Baa, baaa
? Match the first metacharacters 0 or 1 times,/Ba */will match B, Ba
(X) Match X and save X in the variable $1... $9.
X | y Match X or Y
{N} Exact match n times
{N ,} Match more than N times
{N, m} Match N-m times
[Xyz] Character set (Character Set), which matches any one of the characters (or metacharacters) in the set)
[^ XYZ] Does not match any character in this set
[/B] Match a return character
/B Match the boundary of a word
/B Match non-boundary of a word
/CX Here, X is a controller, // cm/matches Ctrl-m
/D Match a word character, // D/=/[0-9]/
/D Match a non-word character, // D/=/[^ 0-9]/
/N Match A linefeed
/R Match a carriage return.
/S Matches a blank character, including/N,/R,/F,/t,/V, etc.
/S Match a non-blank character, equal to/[^/n/F/R/T/V]/
/T Match a tab
/V Match a Duplicate Tab
/W Match a character that can make up a word (alphanumeric, this is my free translation, containing numbers), including underscores, such as [/W] matching 5 in "$5.98", equal to [a-zA-Z0-9]
/W Match a character that cannot make up a word, such as [/W] matching $ in "$5.98", equal to [^ a-zA-Z0-9].


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.