String Pattern Matching in MySQL (1)

MySQL provides standard SQL mode matching and an extended regular expression mode matching format based on Unix utilities such as vi, grep, and sed.

Standard SQL mode matching

SQL pattern matching allows you to use "_" to match any single character, while "%" matches any number of characters (including zero characters ). In MySQL, the SQL mode is case-insensitive by default. Some examples are shown below. Note that when you use SQL mode, you cannot use = or! =; And use the LIKE or not like comparison operator.

For example, in the pet table, to find the name starting with "B:

mysql> SELECT * FROM pet WHERE name LIKE "b%";

+ -------- + --------- + ------ + ------------ +

| Name | owner | species | sex | birth | death |

+ -------- + --------- + ------ + ------------ +

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

| Boane | Diane | dog | m |

+ -------- + --------- + ------ + ------------ +

To find the name ending with "fy:

mysql> SELECT * FROM pet WHERE name LIKE "%fy";

+ -------- + --------- + ------ + ------------ + ------- +

| Name | owner | species | sex | birth | death |

+ -------- + --------- + ------ + ------------ + ------- +

| Fluffy | Harold | cat | f | 1993-02-04 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+ -------- + --------- + ------ + ------------ + ------- +

To find the name containing "w:

mysql> SELECT * FROM pet WHERE name LIKE "%w%";

+ ---------- + ------- + --------- + ------ + ------------ +

| Name | owner | species | sex | birth | death |

+ ---------- + ------- + --------- + ------ + ------------ +

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Boane | Diane | dog | m |

| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |

+ ---------- + ------- + --------- + ------ + ------------ +

To locate the name containing exactly five characters, use the "_" pattern character:

mysql> SELECT * FROM pet WHERE name LIKE "_____";

+ ------- + -------- + --------- + ------ + ------------ + ------- +

| Name | owner | species | sex | birth | death |

+ ------- + -------- + --------- + ------ + ------------ + ------- +

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+ ------- + -------- + --------- + ------ + ------------ + ------- +