Shulou(Shulou.com)06/01 Report--

The fuzzy query statement is as follows: "SELECT field FROM table WHERE a field Like condition".

Mysql provides four matching patterns:

1.% represents any 0 or more characters.

The statement is as follows:

SELECT * FROM user WHERE name LIKE'; 3%'

Duname will be identified as "Zhang San", "three-legged cat", "Tang Sanzang" and so on.

2. _ represents any single character. Statement:

SELECT * FROM user WHERE name LIKE'; _ three _'

Only find out "Tang Sanzang" so that the name is three words and the middle word is "three".

SELECT * FROM user WHERE name LIKE'; 3 _'

Just find out "three-legged cat" so that name is three words and the first word is "three".

3. [] represents one of the characters listed in parentheses (similar to a regular expression).

Statement:

SELECT * FROM user WHERE name LIKE'; [Wang Li] San'

Will find "Zhang San", "Li San" and "Wang San" (instead of "Zhang Li Wang San")

If there are a series of characters (01234, abcde, etc.) in [], they can be abbreviated as "0-4" or "Amure".

SELECT * FROM user WHERE name LIKE'; Old [1-9]'

Will find out "old 1", "old 2", …... , "Lao 9"

If you want to find the "-" character, please put it first:'; Zhang San [- 1-9]'

4. [^] represents a single character that is not listed in parentheses.

Statement:

SELECT * FROM user WHERE name LIKE'; [Zhang Li Wang] San'

Will find out "Zhao San" and "Sun San" who are not surnamed "Zhang", "Li", "Wang", etc.

SELECT * FROM user WHERE name LIKE'; Old [^ 1-4]'

Will rule out "old 1" to "old 4" to find "old 5", "old 6", …... , "Old Nine".

Finally, the point!

Due to wildcards, we can not query the special characters "%", "_", "[", "';", but enclose the special characters in "[]" to make a normal query. Based on this, we write the following functions:

Function sqlencode (str) str=replace (str, ";", ";';) str=replace (str," ["," [[] ")'; this sentence must first str=replace (str," _ "," [_] ") str=replace (str,"% "," [%] ") sqlencode=strend function

What is the above mysql fuzzy query statement? For more details, please pay attention to other related articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.