Regular Expression usage in mysqlsql

Source: Internet
Author: User
Tags character classes
Using Regular Expressions in MySql can get twice the result with half the effort. For example, you can use mysql to filter Chinese records of a field.

Using Regular Expressions in MySql can get twice the result with half the effort. For example, you can use mysql to filter Chinese records of a field.

Using Regular Expressions in MySql can get twice the result with half the effort. For example, you can simply look at the example and filter the Chinese records of a field:

Special characters and structures that can be used for REGEXP operations in MySQL, and some examples are provided. This appendix does not contain all the details that can be found on Henry Spencer's regex (7) manual page. This manual page is included in the regex.7 file in the regex directory of the MySQL distribution edition.
Describes a set of strings. The simplest regular expression is a regular expression without any special characters. For example, the regular expression hello matches hello.
Extraordinary Regular Expressions use special structures so that they can match more than one string. For example, the regular expression hello | word matches the string hello or string word.
As a more complex example, the regular expression B [an] * s matches any of the following strings: Bananas, Baaaaas, Bs, and any other string that starts with B, ends with s, and contains any number of a or n characters

SQL Regular Expression

* From table where not name regexp '^ [1-9A-Za-z]';

Instance

Mysql> SELECT 'axbc' REGEXP '[a-dXYZ]';-> 1
Mysql> SELECT 'axbc' REGEXP '^ [a-dXYZ] $';-> 0
Mysql> SELECT 'axbc' REGEXP '^ [a-dXYZ] + $';-> 1
Mysql> SELECT 'axbc' REGEXP '^ [^ a-dXYZ] + $';-> 0
Mysql> SELECT 'gheis 'regexp' ^ [^ a-dXYZ] + $ ';-> 1
Mysql> SELECT 'gheisa 'regexp' ^ [^ a-dXYZ] + $ ';-> 0

In the brackets expression (using [and]), match the character sequence used for checking the element. The character is a single character or a new line or another character name. In the regexp/cname. h file, you can find the complete list of character names.

mysql> SELECT '~' REGEXP '[[.~.]]';                     -> 1
mysql> SELECT '~' REGEXP '[[.tilde.]]';                 -> 1

· [= Character_class =]

In the brackets (using [and]), [= character_class =] indicates the same type. It matches all characters with the same value, including itself. For example, if both o and (+) are similar members, [[= o =], [[= (+) =], and [o (+)] are synonyms. And so on.

· [: 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 'justalnums' REGEXP '[[:alnum:]]+';       -> 1
mysql> SELECT '!!' REGEXP '[[:alnum:]]+';               -> 0

· [[: <:], [[:>:]

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
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.