MySQL fuzzy query syntax

Welcome to the Linux community forum and interact with 2 million technical staff to enter the standard SQL mode matching provided by MySQL, and an extended regular expression pattern matching format based on Unix utilities such as vi, grep, and sed. SQL pattern matching allows you to use _ to match any single character, while % to match any number of characters (including zero characters)

Welcome to the Linux community forum and interact with 2 million technical staff> enter MySQL to provide standard SQL mode matching, and an extended regular expression pattern matching format based on Unix utilities such as vi, grep, and sed. SQL pattern matching allows you to use "_" to match any single character, while "%" matches any number of characters (including zero characters)

Welcome to the Linux community forum and interact with 2 million technicians>

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

Format of the pattern match.

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 the SQL mode, you do not

Can use = or! =; And use the LIKE or not like comparison operator.

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 a 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 |

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

Other types of pattern matching provided by MySQL use extended regular expressions. Use

REGEXP and not regexp operators (or RLIKE and not rlike, which are synonyms ).

Some Characters of the extended regular expression are:

"." Matches any single character.

One character class "[…] "Matches any character in square brackets. For example, "[abc]" matches "a", "B", or "c ".

To name a range of characters, use a "-". "[a-z]" to match any lowercase letter, and "[0-9]" to match any

What is the number.

"*" Matches zero or multiple items before it. For example, "x *" matches any number of "x" characters, "[0-9] *"

Match any number of digits, while ". *" matches any number of things.

Regular Expressions are case-sensitive, but if you want to, you can use one character class matching method. For example,

"[AA]" matches lowercase or upper-case "a" and "[a-zA-Z]" matches any letter in either of the two statements.

If it appears anywhere in the tested value, the schema matches (as long as they match the entire value, the SQL schema matches ).

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

End with "$ ".

To demonstrate how the extended regular expression works, the LIKE Query shown above is rewritten using REGEXP below:

To find the name starting with "B", use "^" to match the name and use "[bB]" to match "B" in lower case or upper case ":

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

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

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

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

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

| Boane | Diane | dog | m |

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

[1] [2]