Implementation of fuzzy query like%% in MySQL

1, %: represents any 0 or more characters. It can match characters of any type and length. In some cases, if it is Chinese, use two percent signs (%%) to represent it.

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

All records with the letter "three" in u_name, such as "Zhang San", "Zhang Mao San", "Three-legged Cat", "Tang Sanzang", etc., will be found.

In addition, if you need to find records that contain both "三" and "猫" in u_name, use the and condition

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

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

Although you can search for "三脚猫", you cannot search for "张猫三" which meets the criteria.

2, _: represents any single character. Matches a single arbitrary character, which is often used to limit the character length of the expression statement:

For example, SELECT * FROM [user] WHERE u_name LIKE '_三_'
Only find "唐三藏" and other names whose u_name is three characters and the middle character is "三";

For example, SELECT * FROM [user] WHERE u_name LIKE '三__'; only find "三脚猫" and other names with three characters and the first character is "三";

3. [ ]: represents one of the characters listed in the brackets (similar to regular expressions). Specifies a character, string, or range, requiring the match to be any of them.

For example, SELECT * FROM [user] WHERE u_name LIKE '[张李王]三' will find "张三", "李三", "王三" (but not "张李王三");

If there is a series of characters in [ ] (such as 01234, abcde, etc.), it can be abbreviated as "0-4", "ae"

SELECT * FROM [user] WHERE u_name LIKE '老[1-9]' will find "老1", "老2", ..., "老9"; 

4. [^]: represents a single character not listed in the brackets. Its value is the same as [], but it requires the matched object to be any character other than the specified characters.

For example, SELECT * FROM [user] WHERE u_name LIKE '[^张李王]三' will find "赵三", "孙三" and so on who are not named "张", "李", or "王";

SELECT * FROM [user] WHERE u_name LIKE '老[^1-4]'; will exclude "老1" to "老4" and search for "老5", "老6", ...

5. When the query content contains wildcards

Due to the wildcard, our query statements for special characters "%", "_", and "[" cannot be implemented normally. However, we can query normally by enclosing the special characters in "[ ]". Based on this we write the following function:

function sqlencode(str) str=replace(str,"';","';';") 
str=replace(str,"[","[[]") ';

This sentence must be at the beginning str=replace(str,"_","[_]") str=replace(str,"%","[%]") sqlencode=str end function

This is the end of this article about the implementation of like%% fuzzy query in MySQL. For more relevant MySQL like%% fuzzy query content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!