String Pattern Matching in MySQL

MySQL provides standard SQL pattern matching and a format based on pattern matching of extended regular expressions like Unix utilities such as vi, grep and sed.

Standard SQL pattern matching

SQL pattern matching allows you to match any single character with "_" and "%" with any number of characters (including zero characters). In MySQL, the default SQL mode is case-insensitive. Here are some examples. Note that you can not use = or! = While you are in SQL mode, and use LIKE or NOT LIKE to compare operators.

For example, in a table pet, to find the name that starts with "b":

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

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

| name | owner | species | sex | birth | death |

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

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

Bowser | Diane | dog | m | 1989-08-31 |

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

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 that contains a "w":

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

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

| name | owner | species | sex | birth | death |

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

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

Bowser | Diane | dog | m | 1989-08-31 |

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

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

To find out exactly 5 characters, use the "_" pattern:

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 |

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

MySQL provides standard SQL pattern matching and a format based on pattern matching of extended regular expressions like Unix utilities such as vi, grep and sed.

Extended regular expression pattern matching

Other types of pattern matching provided by MySQL are using extended regular expressions. Use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonymous) when you match-test these types of patterns.

Some characters that extend the regular expression are:

Matches any single character.

A character class "[...]" matches any character in square brackets. For example, "[abc]" matches "a", "b" or "c". To name a range of characters, use a "-".

"[Az]" matches any lowercase letter, and "[0-9]" matches any number.

"*" Matches zero or more things in front of it. For example, "x *" matches any number of "x" characters, "[0-9] *" any number of matches, and ". *" Matches any number of anything.

Regular expressions are case sensitive, but you can use one character class to match both wordings, if you wish. For example, "[aA]" matches lowercase or uppercase "a" and "[a-zA-Z]" matches any letter of the two wordings.

If it appears anywhere on the value being tested, the patterns match (as long as they match the entire value, the SQL pattern matches).

To locate a pattern so that it must match the beginning or end of the value being tested, use "^" at the beginning of the pattern or "$" at the end of the pattern.

To illustrate how extended regular expressions work, the LIKE query shown above uses REGEXP rewrite below:

To find names that start with "b", use "^" to match the beginning of the name and "[bB]" to match lowercase or uppercase "b":

mysql> SELECT * FROM pet WHERE name REGEXP "^ [bB]";

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

| name | owner | species | sex | birth | death |

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

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

Bowser | Diane | dog | m | 1989-08-31 |

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

To find the name ending with "fy", use "$" to match the end of the name:

mysql> SELECT * FROM pet WHERE name REGEXP "fy___FCKpd___5" ;;

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

| name | owner | species | sex | birth | death |

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

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

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

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

To find names that contain a "w", use "[wW]" to match the lowercase or uppercase "w":

mysql> SELECT * FROM pet WHERE name REGEXP "[wW]";

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

| name | owner | species | sex | birth | death |

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

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

Bowser | Diane | dog | m | 1989-08-31 |

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

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

Since if a regular expression appears anywhere in the value and its pattern matches, then it is not necessary to place a wildcard on both sides of the pattern in the previous query so that it matches the entire value,

Just like if you used a SQL mode.

To find names that contain exactly five characters, use "^" and "$" to match the beginning and end of the name, and five "." Instances in between:

mysql> SELECT * FROM pet WHERE name REGEXP "^ .....___ FCKpd___7" ;;

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

| name | owner | species | sex | birth | death |

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

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

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

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

You can also override the previous query using the "{n}" "repeat n times" operator:

mysql> SELECT * FROM pet WHERE name REGEXP "^. {5} ___ FCKpd___8quot ;;

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

| name | owner | species | sex | birth | death |

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

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

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

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

to sum up

This article introduced the knowledge of string pattern matching. The standard SQL pattern matching is the standard of the SQL language and can be accepted by other relational database systems. Extended regular expression pattern matching

Is based on the Unix system standard developed, generally only be used on MySQL, but its function than the standard SQL pattern matching stronger.