Home > Database > Mysql Tutorial > body text

MySQL Advanced (6) Introduction to Four Usage of Fuzzy Query

黄舟
Release: 2017-02-09 15:22:42
Original
971 people have browsed it

Introduction to the four usages of fuzzy query in mysql

This article mainly introduces the four usages of fuzzy query in MySQL. Friends in need can refer to it.

The following introduces four uses of fuzzy query in mysql:

1 %:

represents any 0 or more characters. Can match characters of any type and length. In some cases, if it is Chinese, please use two percent signs (%%) to express it.

For example, SELECT * FROM [user] WHERE u_name LIKE '%三%'

will treat u_name as "Zhang San", "Zhang Cat San", "Three-legged Cat", "Tang Tripitaka" and so on, find all the records with "three" in them.

In addition, if you need to find the records with both "三" and "cat" in u_name, please use the and condition

SELECT * FROM [user] WHERE u_name LIKE '%三%' AND u_name LIKE '%猫%'
Copy after login

If you use SELECT * FROM [user] WHERE u_name LIKE ' %三%cat%'

Although the search for "three-legged cat" can be found, the search for "Zhang Cat San" that meets the conditions cannot be found.

2 _:

represents any single character. Matches a single arbitrary character, which is often used to limit the character length of expressions:

For example, SELECT * FROM [user] WHERE u_name LIKE '_三_'

Only find "Tang Sanzang" In this way u_name has three characters and one character in the middle is "三";

Another example is SELECT * FROM [user] WHERE u_name LIKE '三__'; Only find "三级" Cat" has a name of three characters and the first character is "三";

3 [ ]:

represents one of the characters listed in brackets (similar to a regular expression) . Specify a character, string, or range, requiring the matched object to be any one of them.

For example, SELECT * FROM [user] WHERE u_name LIKE '[Zhang Li Wang] San' will find "Zhang San", "Li San", "Wang San" (instead of "Zhang Li Wang San" );

If [ ] contains a series of characters (01234, abcde, etc.), it can be abbreviated as "0-4", "a-e"

SELECT * FROM [user] WHERE u_name LIKE '老[1-9]' 将找出“老1”、“老2”、……、“老9”;
Copy after login

4 [^ ]:

represents a single character not listed in brackets. Its value is the same as [], but it requires that the matched object is any character other than the specified character.

For example, SELECT * FROM [user] WHERE u_name LIKE '[^Zhang Li Wang] San' will find "Zhao San" and "Sun San" whose surnames are not "Zhang", "Li" and "Wang" " etc.;

SELECT * FROM [user] WHERE u_name LIKE '老[^1-4]'; 将排除“老1”到“老4”,寻找“老5”、“老6”、……
Copy after login


5 When the query content contains wildcards

Due to wildcards, we query the special characters "%", "_", " The statement of [" cannot be implemented normally, but the special characters can be queried normally if enclosed with "[]". Based on this, we write the following function:

function sqlencode(str) str=replace(str,"';","';';")
Copy after login

str=replace(str,"[","[[]") ';This sentence must come first str=replace(str,"_", "[_]") str=replace(str,"%","[%]") sqlencode=str end function

The above are the four usages of mysql advanced (6) fuzzy query The content of the introduction, for more related content, please pay attention to the PHP Chinese website (www.php.cn)!


Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template