How does mysql use regular matching to fuzzy query a field

This article mainly explains "mysql how to use regular matching fuzzy query a field", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "mysql how to use regular matching fuzzy query a field" bar!

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

Thank you for your reading, the above is the content of "mysql how to use regular matching fuzzy query a field". After the study of this article, I believe you have a deeper understanding of how mysql uses regular matching fuzzy query to query a certain field, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!