"Go" MySQL Getting started learning two: using regular expressions to search

Reprint Address: http://www.2cto.com/database/201212/173869.html

Introduction to Regular Expressions   www.2cto.com   Regular expressions are special strings (character sets) that are used to match text.   Second, using the MySQL regular expression  1, MySQL only supports a very small subset of the implementation of most regular expressions.  2, like matches the entire column value, and RegExp matches the substring of the column value, if the matched text appears in the column value, RegExp will find it, and the corresponding row is returned. See the following example:  www.2cto.com  [sql] mysql>select username from v9_admin where username like ' space ';  emptyset (0.00 sec)     mysql>select username from v9_admin where username REGEXP ' space ';  +-----------+  |username  |  +-----------+  |warmspace |  +-----------+  1 Rowin Set (0.02 sec)  3, default MySQL regular expression matches are case-insensitive and can be case-sensitive using the binary keyword, such as  [sql]  Mysql>select username from v9_admin      where username REGEXP BINARY ' spacce ';  emptyset (0.04 sec)  4, do or match: for searching one of two strings, use | as follows:  [sql] mysql>select Roleid from V9_admin_ Role      where Roleid REGEXP ' 1|3 ';  +--------+  |roleid |  +--------+  |      1 |  |      3 |  +--------+  2 Rowsin Set (0.00 sec)  5, matches one of several characters: matches any single character. As follows:  [sql] mysql>select name from V9_collection_node     , where name REGEXP ' 005-[123 ]‘;  +-----------------+  |name            |  +-----------------+  |101-02-01-005-2 |  |101-02-01-005-1 |  |101-02-01-005-3 |  +-----------------+  3 Rowsin Set (0.00 sec)   As you can see, [] is another form of an or statement. ' 005-[123] ' is equivalent to ' 005-[1|2|3 '.   matches characters except for a given character:  [sql] mysql>select name from V9_collection_node     , where name REGEXP ' 005-[^123] ';  +-------------------+  |name              |  +-------------------+  |101-02-01-005-4.2 |  |101-02-01-005-4   |  |101-02-01-005-5   |  +-------------------+  3 Rowsin Set (0.00 sec)  6, can be used-to define a range. such as [1-9],[a-b].  [sql] mysql>select name from V9_collection_node      WHERE name REGEXP ' 005-[1-3] ';  +-----------------+  |name            |  +-----------------+  |101-02-01-005-2 |  |101-02-01-005-1 |  |101-02-01-005-3 |  +-----------------+  3 Rowsin Set (0.00 sec)  7, in order to match Special characters, must be preceded by \ \, for example \\-,\\. This processing is escaped (escaping).   Most regular expression implementations use a single backslash to escape special characters, but MySQL requires two (MySQL itself interprets one, and the regular expression library interprets the other).  \\ is also used to refer to metacharacters (characters with special meanings), such as the following table:   8, for easier work, you can use predefined character sets, called character classes (character Class).  [sql] mysql> Select name Fromv9_collection_node     -> where name REGEXP ' [[:d igit:]] ';  +-------------------+  | Name              |  +-------------------+  | 101-10-01-002-1   |  | 101-02-01-005-4.2 |  | 101-02-01-005-2   |  | 101-02-01-005-1   |  | 101-02-01-005-3   |  | 101-02-01-005-4   |  | 101-02-01-005-5   |  | 101-10-01-002-2   |  | 101-11-04-001-1   |  +-------------------+  9 Rowsin Set (0.00 sec)  

9. Use regular expression repeating meta characters to match multiple instances: [SQL] mysql> select name Fromv9_collection_node, where name REGEXP ' 1{2} '; +-----------------+  |  name | +-----------------+  |  101-11-04-001-1 | +-----------------+ 1 Rowin Set (0.00 sec)

10. Locator (in order to match text at a specific location): [SQL] mysql> select name from V9_collection_node, where name REGEXP ' 2$ '; +-------------------+  |  name | +-------------------+  |  101-02-01-005-4.2 | |  101-02-01-005-2 | |  101-10-01-002-2 | +-------------------+ 3 rows in Set (0.00 sec)

 11, make regexp similar to the role of like, the difference is that it matches the entire string and regexp matching substring. With a locator, each expression is ended with $ by starting each expression with ^.  12, simple Regular expression test: You can test a regular expression with select without using a database table. The RegExp check always returns 0 or 1 (match).  [sql] mysql> select ' Hello ' REGEXP ' [0-9] ';  +------------------------+  | ' Hello ' REGEXP ' [0-9] ' |  +------------------------+  |                      0 |  +------------------------+  1 row in Set (0.00 sec)