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

When we query a certain field, sometimes we just want to match a certain piece of data, for example, if we want to query whether all the keywords in this article have a certain keyword, we often need to match. Let's explain how to match the value of a key query.

The syntax of the SQL fuzzy query is

"SELECT column FROM table WHERE column LIKE'; pattern';".

SQL provides four matching patterns:

1.% represents any 0 or more characters. The statement is as follows:

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

Name 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 come first.

Str=replace (str, "_", "[_]")

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

Sqlencode=str

End function

These are the details of a field queried by mysql regular matching fuzzy method. Please pay attention to other related articles for more information.

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.