MySQL Fuzzy query

Instance:

For SQL Fuzzy queries, using the like comparison keyword, plus the wildcard characters in SQL, refer to the following:
1. Like ' mc% ' will search for all strings (such as McBadden) that begin with the letter Mc.
2. Like '%inger ' will search for all strings ending with the letter inger (such as Ringer, Stringer).
3. Like '%en% ' will search all strings containing the letter en in any location (e.g. Bennet, Green, McBadden).
4. Like ' _heryl ' will search for all six-letter names (such as Cheryl, Sheryl) ending with the letter heryl.
5. Like ' [Ck]ars[eo]n ' will search for the following strings: Carsen, Karsen, Carson, and Karson (such as Carson).
6. Like ' [M-z]inger ' will search for all names (such as Ringer) ending with the string inger, starting with any single letter from M to Z.
7, like ' m[^c]% ' will search starts with the letter M, and the second letter is not all names of C (such as Macfeather).

Theory:

SELECT field from table WHERE a field like condition

With regard to the conditions, SQL provides four matching modes:

1,%: Represents any 0 or more characters. Can match any type and length of the character, in some cases, if Chinese, please use two percent sign (%).

For example SELECT * from [user] WHERE u_name like '% three '

will be u_name for "Zhang San", "Zhang Cat Three", "three-legged Cat", "Tang Sanzang" and so on Have "three" records all find out.

Also, if you need to find a record of "three" and "cat" in U_name, use the 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 feet cat", but can not search out the eligible "Zhang Cat three".

2,_: Represents any single character. Matches a single arbitrary character, which is commonly used to restrict the expression's character-length statement:

For example SELECT * from [user] WHERE u_name like ' _ Three _ '
Only find "Tang Sanzang" so u_name for three words and the middle of a word is "three";

Another example is SELECT * from [user] WHERE u_name like ' three __ ';
Just find out "three-legged cat" this name is three words and the first word is "three";


3,[]: represents one of the characters listed in parentheses (similar to a regular expression). Specifies a character, string, or range that requires matching objects to be any of them.

such as SELECT * from [user] WHERE u_name like ' [Zhang Li Wang] three '
Will find "Zhang San", "Lie Triple", "Wang San" (not "Zhangli Kang");

such as [] a series of characters (01234, ABCDE, etc.) can be slightly written as "0-4", "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 that is not listed within the parentheses. The value is the same as [], but it requires that the matched object be any character other than the specified character.

such as SELECT * from [user] WHERE u_name like ' [^ Zhang Li Wang] three '
Will find the surname "Zhang", "Li", "Wang" "Zhao Three", "Magozo" and so on;

SELECT * FROM [user] WHERE u_name like ' old [^1-4] ';
Will exclude "old 1" to "Old 4", Looking for "old 5", "Old 6" 、......

5, when the query contains a wildcard character

Because of the wildcard character, which causes us to query the special characters "%", "_", "[" the statement can not be implemented normally, and the special characters with "[]" in the normal query. Thus we write the following function:

function Sqlencode (str) Str=replace (str, "[", "[[]") ' This sentence must be in the top Str=replace (str, "_", "[_]") Str=replace (str, "%", "[%]") Sqlencode=str End Function

The unknown origin string is processed by the function before the query, and it is important to note that when a database is connected to a Web page using this type of query statement:

such as SELECT * from user Where name is like ' old [^1-4] '; above "old [^1-4]" ' "is to have single quotation marks, do not forget, I often forget!

MySQL Fuzzy query