Four matching patterns && regular expressions for commonly used fuzzy queries in SQL

When executing a database query, there is a complete query and a fuzzy query.
The general fuzzy statement is as follows:
SELECT field from table WHERE a field like condition

With regard to the conditions, SQL provides four matching modes:
1,%: denotes any 0 or more characters . Can match any type and length of characters, in some cases if Chinese, please use two percent sign (%).
For example SELECT * from [user] WHERE u_name like '% three '
will be u_name for "Zhang San", "Zhang Cat Three", "three-legged Cat", "Tang Sanzang" and so on Have "three" records all find out.
In addition, if you need to find the u_name in the "three" and "Cat" record, please apply and conditions
SELECT * FROM [user] WHERE u_name like '% three ' and u_name like '% cat% '
If using SELECT * from [user] WHERE u_name like '% cat% '
Although can search out "three feet cat", but can not search out the eligible "Zhang Cat three".

2,_: Represents any single character. Matches a single arbitrary character , which is commonly used to qualify an expression for a character-length statement:
For example SELECT * from [user] WHERE u_name like ' _ Three _ '
Only find "Tang Sanzang" so u_name for three words and the middle of a word is "three";
Another example is SELECT * from [user] WHERE u_name like ' three __ ';
Just find out "three-legged cat" this name is three words and the first word is "three";

3,[]: represents one of the characters listed in parentheses (similar to a regular expression). Specifies a character, string, or range that requires matching objects to be any of them.
such as SELECT * from [user] WHERE u_name like ' [Zhang Li Wang] three '
Will find "Zhang San", "Lie Triple", "Wang San" (not "Zhangli Kang");
such as [] a series of characters (01234, ABCDE, etc.) can be slightly written as "0-4", "A-E"
SELECT * FROM [user] WHERE u_name like ' old [1-9] '
Will find "Old 1", "Old 2" 、......、 "Old 9";

4,[^]: Represents a single character that is not listed in parentheses . The value is the same as [], but it requires that the matched object be any character other than the specified character.
such as SELECT * from [user] WHERE u_name like ' [^ Zhang Li Wang] three '
Will find the surname "Zhang", "Li", "Wang" "Zhao Three", "Magozo" and so on;
SELECT * FROM [user] WHERE u_name like ' old [^1-4] ';
Will exclude "old 1" to "Old 4", Looking for "old 5", "Old 6" 、......

5. When the contents of the query include a wildcard
Because of the wildcard character, which causes us to query the special characters "%", "_", "[" the statement can not be implemented normally, and the special characters with "[]" can be queried normally . Thus we write the following function:
function Sqlencode (str)
Str=replace (str, "[", "[[]") ' This sentence must be in the top
Str=replace (str, "_", "[_]")
Str=replace (str, "%", "[%]")
Sqlencode=str
End Function
The unknown origin string is processed by the function before the query.

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

When you test for this type of pattern, use the regexp and not regexp operators (or rlike and not rlike, which are synonyms).
Some of the characters that extend the regular expression are:
“.” matches any single character.
A character class "[...]" Matches any character within the square brackets. For example,"[ABC]" matches "a", "B", or "C". To name a range of characters, use a "-". "[A-z]" matches any lowercase letter, while "[0-9]" matches any number.
"*" matches 0 or more things in front of it. For example, "x*" matches any number of "X" characters, "[0-9]*" matches any number of numbers, and ". *" matches any number of anything.

Regular expressions are case-sensitive, but if you want, you can use a character class to match two ways of writing. For example, "[AA]" matches lowercase or uppercase "a" and "[A-za-z]" matches any letter of two notation.
If it appears anywhere in the value being tested, the pattern matches (as long as they match the entire value, the SQL pattern matches).
In order to locate a pattern so that it must match the beginning or end of the value being tested, in the modeuse "^" at the beginning or "$" at the end of the pattern。
To illustrate how an extended regular expression works, the like query shown above uses RegExp rewrite below:
To find the name starting with "B", use "^"MatchStart of the name and "[BB]" matchlowercase 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 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
To find the name ending with "FY", use "$" to match the end of the name:
Mysql> select * FROM pet WHERE name REGEXP "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", use "[WW]" to match 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 | 1995-07-29 |
| Whistler | Gwen | Bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+
Now that if a regular expression appears anywhere in the value and its pattern matches, you don't have to put a wildcard in the two aspects of the pattern in the previous query so that it matches the entire value, just as if you were using a SQL schema.
To find out what containsexactly 5 charactersName, use "^" and "$" to match the beginning and end of the name, and 5 "." The instance is between the two:
Mysql> SELECT * from pet WHERE name REGEXP "^.....$";
+-------+--------+---------+------+------------+-------+
| name | Owner | Species | sex | Birth | Death |
+-------+--------+---------+------+------------+-------+
| Hahah | Hahah | Cat | m | 1990-03-15 | NULL |
| Buffy | Harold | Dog | f | 1989-05-20 | NULL |
+-------+--------+---------+------+------------+-------+
You can also use"{n}" "Repeat N Times"The operator overrides the previous query:
Mysql> select * FROM pet WHERE name REGEXP "^. {5}$ ";
+-------+--------+---------+------+------------+-------+
| Name
| Owner | Species | sex | Birth | Death |
+-------+--------+---------+------+------------+-------+
| Hahah | Hahah | Cat | m | 1990-03-15 | NULL |
| Buffy | Harold | Dog | f | 1989-05-20 | NULL |
+-------+--------+---------+------+------------+-------+

Four matching patterns && regular expressions for commonly used fuzzy queries in SQL