MYSQL 2: use regular expressions to search

MYSQL Introduction 2: use regular expressions to search 1. Regular Expressions introduction www.2cto.com regular expressions are special strings used to match text (Character Set combination ). Ii. use MySQL Regular Expression 1. MySQL only supports a small subset implemented by most regular expressions. 2. LIKE matches the entire column value, while REGEXP matches the sub-string of the column value. If the matched text appears in the column value, REGEXP will find it and the corresponding row will be 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. By default, regular expression matching in MySQL is case insensitive, BINARY keywords can be used to distinguish between Case sensitivity, such as [SQL] mysql> select username from v9_admin-> where username REGEXP BINARY 'spacce '; Empt Yset (0.04 sec) 4. Perform OR match: To search for one of the two strings, use |, as shown below: [SQL] mysql> select roleid from v9_admin_role-> where roleid REGEXP '1 | 3 '; + -------- + | roleid | + -------- + | 1 | 3 | + -------- + 2 rowsin set (0.00 sec) 5. match any single character. [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 OR statement. '005-[123] 'is equivalent to '005-[1 | 2 | 3. Match the characters except the given characters: [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. You can use-to define a range. For example, [1-9] and [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. To match special characters, \ must be used as the leading character, for example \\-,\\.. This processing is an escape (escaping ). Most regular expressions use a single backslash to escape special characters, but MYSQL requires two (MYSQL interprets one by itself, and the regular expression library interprets the other ). \ Is also used to reference metacharacters (characters with special meanings), as shown in the following table: 8. For convenience, you can use a predefined character Set, called a character class ). [SQL] mysql> select name fromv9_collection_node-> where name REGEXP '[[: digit:]'; + ------------------- + | 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-1- 01-005-5 | 101-10-01-002-2 | 101-11-04-001-1 | + ----------------- + 9 rowsin set (0.00 sec)

9. Use regular expressions to duplicate metacharacters 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. Location operator (to match the text at a specific position): [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. The difference between REGEXP and LIKE is that LIKE matches the entire string and REGEXP matches the substring. Use the locator to start each expression with ^ and end each expression with $. 12. Test A simple regular expression: You can use SELECT to test the regular expression without using a database table. The REGEXP check always returns 0 or 1 (matching ). [SQL] mysql> select 'Hello 'regexp '[0-9]'; + ------------------------ + | 'Hello 'regexp '[0-9]' | + ------------------------ + | 0 | + -------------------- + 1 row in set (0.00 sec)