MySQL learning footprint record 07 -- Data Filtering -- Retrieval Using Regular Expressions

MySQL learning footprint record 07 -- Data Filtering -- use regular expressions to retrieve the search data used in this article

    mysql> SELECT prod_name FROM products             -> ORDER BY prod_name;+----------------+| prod_name      |+----------------+| .5 ton anvil   || 1 ton anvil    || 2 ton anvil    || Bird seed      || Carrots        || Detonator      || Fuses          || JetPack 1000   || JetPack 2000   || Oil can        || Safe           || Sling          || TNT (1 stick)  || TNT (5 sticks) |+----------------+14 rows in set (0.00 sec)

 

1. Basic Character matching

Eg: mysql> SELECT prod_name FROM products-> WHERE prod_name REGEXP '000000' # match "1000"-> order by prod_name; + -------------- + | prod_name | + -------------- + | JetPack 1000 | + -------------- + 1 row in set (0.00 sec) eg: mysql> SELECT prod_name FROM products-> WHERE prod_name REGEXP '. 000 '#'. 'indicates matching any character-> order by prod_name; + -------------- + | prod_name | + -------------- + | JetPack 1000 | JetPack 2000 | + -------------- + 2 rows in set (0.00 sec)

 

2. Perform OR matching. to search for one of N strings, use '|'

  eg:  mysql> SELECT prod_name FROM products           -> WHERE prod_name REGEXP '1000 | 2000'           -> ORDER BY prod_name;+--------------+| prod_name    |+--------------+| JetPack 1000 || JetPack 2000 |+--------------+2 rows in set (0.00 sec)

 

3. match one of several characters * match a specific single character, which can be completed by specifying a set of [] characters

Eg: mysql> SELECT prod_name FROM products-> WHERE prod_name REGEXP '[123] Ton'-> order by prod_name; + ------------- + | prod_name | + ------------- + | 1 ton andevil | 2 ton andevil | + ------------- + 2 rows in set (0.00 sec) is equivalent: mysql> SELECT prod_name FROM products-> WHERE prod_name REGEXP '[1 | 2 | 3] Ton'-> order by prod_name; + ------------- + | prod_name | + ------------- + | 1 ton andevil | 2 ton andevil | + ------------- + 2 rows in set (0.00 sec)

 

4. Denying a character set '^'

   eg:    mysql> SELECT prod_name FROM products             -> WHERE prod_name REGEXP '[^123] Ton'             -> ORDER BY prod_name;+--------------+| prod_name    |+--------------+| .5 ton anvil |+--------------+1 row in set (0.00 sec)

 

5. Match range [n-m]

  eg:  mysql> SELECT prod_name FROM products            -> WHERE prod_name REGEXP '[1-5] Ton'           -> ORDER BY prod_name;+--------------+| prod_name    |+--------------+| .5 ton anvil || 1 ton anvil  || 2 ton anvil  |+--------------+3 rows in set (0.00 sec)

 

Note: The table data used in the following operations

mysql> SELECT vend_name FROM vendors ORDER BY vend_name;+----------------+| vend_name      |+----------------+| ACME           || Anvils R Us    || Furball Inc.   || Jet Set        || Jouets Et Ours || LT Supplies    |+----------------+6 rows in set (0.00 sec)

 

6. To match special characters, use \ as the leading character, that is, the escape character * MySQL requires two backslashes (one for MySQL, and the other for the Regular Expression Library) to match '.'

Eg: mysql> SELECT vend_name FROM vendors-> WHERE vend_name REGEXP '. '# The Escape Character is not used, so it is not the expected result-> order by vend_name; + ------------------ + | vend_name | + -------------- + | ACME | Anvils R Us | Furball Inc. | Jet Set | Jouets Et Ours | LT Supplies | + ---------------- + 6 rows in set (0.00 sec) correct: mysql> SELECT vend_name FROM vendors-> WHERE vend_name REGEXP '\\. '-> order by vend_name; + -------------- + | vend_name | + -------------- + | Furball Inc. | + -------------- + 1 row in set (0.00 sec)

 

# For reference only, listed below 7, 8, 9, and 10, you do not need to remember 7. match character classes [: alnum:] ==> [a-zA-Z0-9] [: alpha:] ==> [a-zA-Z] [: blank:] ==> space and Tab [\ t] [: cntrl:] ==> ASCII control characters (ASCII0 to 31 and 127) [: digit:] ==> [0-9] [: graph:] ==> same as [: print:], but not including spaces [: lower:] ==> [a-z] [: punct:] ==> that is, any character not in [: alnum:] and not in [: cntrl:] [: space:] ==> any blank characters including spaces [\ f \ n \ r \ t \ v] [: upper:] ==> [A-Z] [: xdigit:] ==> any hexadecimal number [a-fA-F0-9] [: print:] ==> any printable character 8. blank metacharacters \ f ==> form feed \ r ==> back Vehicle \ v ==> vertical tabulation 9. match multiple instance repeated metacharacters * ==> 0 or multiple matches + ==> 1 or multiple matches (equal to {1 ,})? ==> 0 or 1 matches (equal to {0, 1}) {n }==> specified number of matches {n ,} ==> not less than the range of {n, m }=> matches (m <= 255)

Eg: mysql> SELECT prod_name FROM products-> WHERE prod_name REGEXP '\ ([0-9] sticks? \\)'#'? 'Match zero or one occurrence of any character before it-> order by prod_name; + ---------------- + | prod_name | + ---------------- + | TNT (1 stick) | TNT (5 sticks) | + ---------------- + 2 rows in set (0.00 sec) mysql> SELECT prod_name FROM products-> WHERE prod_name REGEXP '[[: digit:] {4} '# match any four digits connected together-> order by prod_name; + -------------- + | prod_name | + -------------- + | JetPack 1000 | JetPack 2000 | + -------------- + 2 rows in set (0.00 sec)

10. positioning character * positioning metacharacters ^ => Start of text $ => end of Text [[: <:] => Start of a word [[: >:]] ==> end of a word

Eg: mysql> SELECT prod_name FROM products-> WHERE prod_name REGEXP '^ [0-9 \.] '#' ^ 'to start with the string, [0-9 \.] indicates that only '. 'Or any number is the first character in the-> order by prod_name; # string to match it + -------------- + | prod_name | + -------------- + |. 5 ton andevil | 1 ton andevil | 2 ton andevil | + -------------- + 3 rows in set (0.00 sec)

 

11. double purpose of '^': used to deny the set in the set '[]'. Otherwise, used to indicate the start of the string.