MySQL fuzzy query statement collection

SQL fuzzy query statement

The general fuzzy statement syntax is as follows:

SELECT field FROM table WHERE a field Like condition

Regarding conditions, SQL provides four matching modes:

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.

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 using

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:

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

Only find "唐三藏" and other names whose u_name is three characters and the middle character is "三";

SELECT * FROM [user] WHERE u_name LIKE '三__';

Only find names with three characters such as "三脚猫" 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.

SELECT * FROM [user] WHERE u_name LIKE '[张李王]三'

will find "Zhang San", "Li San", "Wang San" (but not "Zhang Li Wang San");

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 "Old 1", "Old 2", ..., "Old 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.

SELECT * FROM [user] WHERE u_name LIKE '[^张李王]三'

We will find "Zhao San", "Sun San", etc. who are not named "Zhang", "Li", or "Wang";

SELECT * FROM [user] WHERE u_name LIKE '老[^1-4]';

We will exclude "old 1" to "old 4" and look for "old 5", "old 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,"[","[[]") 'This sentence must be at the beginning str=replace(str,"_","[_]")

str=replace(str,"%","[%]")

sqlencode=str

end function

Before querying, the string to be searched can be processed by this function.

The above is the detailed content of SQL fuzzy query statement. If you have any additions, please contact the editor of 123WORDPRESS.COM.