MySQL must know---wildcard characters and regular expressions

Like operator

To use wildcard characters in a search clause, you must use the LIKE operator.

Note: When is the operator not an operator? The answer is when it is used as a predicate.

2. Percent percent (%) wildcard character

% indicates any number of occurrences of any character.

MariaDB [test]> Select Id,age,province
-From user
, where province like ' Day% '
;
+----+------+----------+
| ID | Age | Province |
+----+------+----------+
| 3 | 56 | Tianjin |
| 9 | 33 | Tianjin |
+----+------+----------+
2 rows in Set (0.00 sec)

3. Underline (_) wildcard characters

Underline _, matching only a single character instead of multiple characters.

Tips for using wildcard characters:
1. Do not overuse wildcard characters
2. When you do need to use wildcards, do not use at the beginning of the search pattern. Impact efficiency
3. Pay close attention to the position of the wildcard character

4. Using the MySQL regular expression

MariaDB [test]> Select Id,age,province
-From user
--where age regexp ' 2 '
;
+----+------+----------+
| ID | Age | Province |
+----+------+----------+
| 1 | 22 | Beijing |
| 2 | 25 | Guangdong |
| 10 | 27 | Hunan |
| 11 | 29 | Beijing |
| 13 | 24 | Beijing |
+----+------+----------+
5 rows in Set (0.00 sec)

5. Make or Match

MariaDB [test]> Select Id,age,province
-From user
-Where age regexp ' 22|27 ';
+----+------+----------+
| ID | Age | Province |
+----+------+----------+
| 1 | 22 | Beijing |
| 10 | 27 | Hunan |
+----+------+----------+
2 rows in Set (0.03 sec)

6. Match one of several characters

MariaDB [test]> Select Id,age,province
-From user
--where Age regexp ' [123]7 ';
+----+------+----------+
| ID | Age | Province |
+----+------+----------+
| 8 | 17 | Hebei |
| 10 | 27 | Hunan |
+----+------+----------+
2 rows in Set (0.00 sec)

7. Match Range

MariaDB [test]> Select Id,age,province
-From user
--where Age regexp ' [1-3]7 ';
+----+------+----------+
| ID | Age | Province |
+----+------+----------+
| 8 | 17 | Hebei |
| 10 | 27 | Hunan |
+----+------+----------+
2 rows in Set (0.00 sec)

8. Match Special characters

Note: Special character handling needs to be escaped escaping

\f Page Change
\ nthe line break
\ r Enter
\ t watchmaking
\v longitudinal watchmaking

Match (\) in order to match the backslash character itself, you need to use \

9. Matching character classes

[: alnum:] Any letter and number
[: Alpha:] any character
[: Blank:] spaces and tabulation
[: Cntrl:] ASCII control characters
[:d igit:] any number
[: Graph:] Same as [:p rint:], but does not include spaces
[: Lower:] Any lowercase letter
[:p rint:] any printable character
[:p UNCT:] Neither [: alnum:] Nor any of the characters in [: Cntrl:]
[: space:] Any white space character, including spaces
[: Upper:] Any uppercase letter
[: xdigit:] any hexadecimal digit

10. Matching multiple instances

• 0 or more matches
• 1 or more matches
  ? 0 or one match
  {n} specified number of matches
  {n,} not less than a specified number of matches
  {N,m} scope match

11. Locator

^ Text start
$ text End
[[: <:]] The beginning of the word
[[:]: The end of the word

MySQL must know---wildcard characters and regular expressions