A brief introduction to MySQL fuzzy query like wildcard characters

% stands for random multiple characters _ Represents a character in MySQL. The default mode for SQL is to ignore uppercase and lowercase
regular mode uses the regexp and not regexp operators。
“.” Matches no matter what the individual characters are. A character class
“［... ] "matches arbitrary individual characters within square brackets
"*" matches 0 or more things in front of it
The normal form is a distinction between uppercase and lowercase. But suppose you want to, you can use a character class to match two types of writing.

Like what. "[AA]" matches lowercase or uppercase "a" and "[A-za-z]" matches either of the letters.
Use "^" at the beginning of the pattern or "$" at the end of the pattern.

In order to find the name that begins with "three". Use "^" to match the beginning of the name.

From [user] WHERE u_name REGEXP ' ^ three ';

will be u_name for "three-legged cat" and so on "three" the beginning of the record to find out all.

To find the name ending with "three", use "$" to match the end of the name.

From [user] WHERE u_name REGEXP ' three $ ';

will be u_name as "Zhang San". "Zhang Cat three" and so on "three" end of the records are all found out.

You can also rewrite the previous query using the "{n}" "N-Times" operator:

From [user] WHERE u_name REGEXP ' b{2}$ ';

The wildcard character in the like statement for MySQL: percent, underscore, and escape%: denotes random or multiple characters. Characters that can match arbitrary types and lengths.     SQL code SELECT * from user where username like '%huxiao ';     SELECT * from user where username like ' huxiao% '; SELECT * from user where username like '%huxiao% ';

Other than that. Suppose you need to find a record of "three" and "cat" in U_name, use an AND condition

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-legged cat", but can not search out the eligible "Zhang Cat three".

_: Represents a random single character. Matches a single random character, which is often used to limit the expression's character-length statement: (can represent a Chinese character）

SQL code SELECT * from user where username like ' _ ';     SELECT * from user where username like ' Huxia_ ';     SELECT * from user where username like ' H_xiao '; Suppose I really want to check% or _, how to do? Use escape, the escape character after the% or _ is not used as a wildcard, note that there is no escape character before the% and _ still play wildcard function SQL code Select Username from Gg_user where username like '%xiao/_% '     Escape '/'; Select username from Gg_user where username like '%xiao/%% ' escape '/';

Second, the regular mode

The other types of patterns that are provided by MySQL are the use of extended regular expressions. When you match 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 normal expression are:

“.” Matches no matter what the individual characters are. ( single-byte characters )

A character class "[... ] "matches whatever character is inside the square brackets.

For example, "[ABC]" matches "a", "B" or "C".

To name a range of characters. Use a "-". "[A-z]" matches no matter what lowercase letters, and "[0-9]" matches no matter what number.

"*" matches 0 or more things in front of it. For example, "x*" matches no matter what number of "X" characters, "[0-9]*" matches whatever number of numbers, while ". *" matches no matter what number of whatever.

The normal form is a distinction between uppercase and lowercase, but suppose you want to. You can use a character class to match two types of notation. Like what. "[AA]" matches lowercase or uppercase "a" and "[A-za-z]" matches either of the letters.

Let's say it's out of the test now, wherever it's measured. Patterns are matched (just want them to match the entire value.) SQL pattern matching).

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

To illustrate how the extended normal table works, the like query you see above is rewritten with the following using regexp:

In order to find the name that begins with "three". Use "^" to match the beginning of the name.

From [user] WHERE u_name REGEXP ' ^ three ';

will be u_name for "three-legged cat" and so on "three" the beginning of the record to find out all.

To find the name ending with "three", use "$" to match the end of the name.

From [user] WHERE u_name REGEXP ' three $ ';

Will devote u_name to "Joe Smith". "Zhang Cat 3" and so on. "Three" is found at the end of the entire record.

You can also overwrite the previous query with the "{n}" "Repeat N Secondary" operation:

From [user] WHERE u_name REGEXP ' b{2}$ ';

Note: If you want to use Chinese text, you may need to be aware of it.

A brief introduction to MySQL fuzzy query like wildcard characters