MySql fuzzy query Summary

In MySql, how does one use fuzzy search? In the Where clause, you can use the Like clause with wildcards to select columns of the datetime, char, and varchar field types... "data record. The following are wildcard characters that can be used: % zero or multiple characters _ single any character (underline) Special Character [] characters in a certain range, such [

In MySql, how does one use fuzzy search? In the Where clause, you can use the Like clause with wildcards to select columns of the datetime, char, and varchar field types... "data record. The following are wildcard characters that can be used: % zero or multiple characters _ single any character (underline) \ Special Character [] characters in a certain range, such [

In MySql, how does one use fuzzy search?

In the Where clause, you can use the Like clause with wildcards for columns of the datetime, char, and varchar field types to select data records that are similar to.... The following are available wildcards:

% Zero or multiple characters
_ Any single character (underline)
\ Special characters
[] Characters in a certain range, such as [0-9] or [aeth]
[^] Characters out of a certain range, such as [^ 0-9] or [^ aeth]

SQL provides four matching modes for conditions:

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

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

We will find all records with "3", such as "3 Zhang", "3 Zhang", "3 Zhang Mao", and "3 Tang sanzang.

In addition, if you need to find records with "three" and "cat" in u_name, use the and condition.
SELECT * FROM [user] WHERE u_name LIKE '% 3%' AND u_name LIKE '% cat %'

If SELECT * FROM [user] WHERE u_name LIKE '% 3% cat %' is used'
Although three-legged cats can be searched, three-legged cats cannot be searched ".

2, _: represents any single character. Matches any character. It is often used to limit the character length of an expression:

For example, SELECT * FROM [user] WHERE u_name LIKE '_ 3 _'
Only find that the u_name such as "Tang sanzang" is three characters and the middle word is "three;

For example, SELECT * FROM [user] WHERE u_name LIKE 'three __';
Find out that the name of the "three-legged cat" is three characters and the first word is "three;

3, []: represents one of the characters listed in brackets (similar to a regular expression ). Specifies a character, string, or range. The matching object must be one of them.

For example, SELECT * FROM [user] WHERE u_name LIKE '[Zhang Li Wang] 3'
We will find "Zhang San", "Li San", and "Wang San" (rather than "Zhang Li Wang San ");

For example, if [] contains a series of characters (01234, abcde, etc.), it can be slightly written as "0-4" or "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 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.

For example, SELECT * FROM [user] WHERE u_name LIKE '[^ Zhang Li Wang] 3'
We will find "Zhao San" and "Sun San" without the surname "Zhang", "Li", and "Wang;

SELECT * FROM [user] WHERE u_name LIKE 'Old [^ 1-4] ';
From "Old 1" to "old 4", search for "old 5", "old 6 ",......

5. When the query content contains wildcards

The special characters "%", "_", and "[" cannot be properly queried due to wildcards, when special characters are included in "[]", they can be queried normally. Then, write the following functions:

Function sqlencode (str) str = replace (str, "[", "[[]") 'must be at the beginning of str = replace (str ,"_", "[_]") str = replace (str, "%", "[%]") sqlencode = str end function

The string to be queried can be processed by this function before query.

Here are a few cases

(1) To find the name starting with "B:

Mysql> SELECT * FROM pet WHERE name LIKE "B % ";

(2) To find the name ending with "fy:

Mysql> SELECT * FROM pet WHERE name LIKE "% fy ";

(3) To find the name containing "w:

Mysql> SELECT * FROM pet WHERE name LIKE "% w % ";

(4) to find a name that contains exactly five characters, use the "_" pattern character:

Mysql> SELECT * FROM pet WHERE name LIKE "_____";

To demonstrate how the extended regular expression works, the LIKE Query shown above is rewritten using REGEXP below:

To find the name starting with "B", use "^" to match the start of the name and use "[bB]" to match "B" in lower case or upper case ":

Mysql> SELECT * FROM pet WHERE name REGEXP "^ [bB]";

To find the name ending with "fy", use "$" to match the end Of the name:

Mysql> SELECT * FROM pet WHERE name REGEXP "fy $ ";

To locate the name containing a "w", use "[wW]" to match the "w" in lower case or upper case ":

Mysql> SELECT * FROM pet WHERE name REGEXP "[wW]";

Since a regular expression appears anywhere in the value and its pattern matches, there is no need to look at the two

Put a wildcard to match the entire value, just as if you use an SQL mode.

To find a name that contains exactly five characters, use "^" and "$" to match the start and end of the name, and use the five "." instances in

Between the two:

Mysql> SELECT * FROM pet WHERE name REGEXP "^... $ ";

You can also use the "{n}" "Repeat n times" operator to rewrite the previous query:

Mysql> SELECT * FROM pet WHERE name REGEXP "^. {5} $ ";

Search for numbers and other fuzzy query statements
Select * from pet where name REGEXP "[^ a-zA-Z].";