MySQL--Four methods of fuzzy query

Excerpt from: http://www.jb51.net/article/48315.htm

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 (%).

Like what

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.

Other than that

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 you use

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:

Like what

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

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

Like what

SELECT * FROM [user] WHERE u_name like ' [Zhang Li Wang] three '

Will find "Zhang San", "Lie Triple", "Wang San" (not "Zhangli Kang");
Such as

[] There are 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 in parentheses. The value is the same as [], but it requires that the matched object be any character other than the specified character.
Like what

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, query content contains wildcard characters
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, "';", "'; ';")
Str=replace (str, "[", "[[]") '; This sentence must be in the first str=replace (str, "_", "[_]") Str=replace (str, "%", "[%]") Sqlencode=str end function

MySQL--Four methods of fuzzy query