mysql regular matching fuzzy method to query a certain field

mysql regular matching fuzzy query for a certain field

When we query a certain field, sometimes we only If we want to match a certain piece of data, for example, if we want to query all keywords in this article to see if a certain keyword exists, we often need to match. Let’s explain how to match and query a certain keyword value

SQL fuzzy query The syntax is
"SELECT column FROM table WHERE column LIKE ';pattern';".

SQL provides four matching modes:
1. % represents any 0 or more characters. The following statement:
SELECT * FROM user WHERE name LIKE ';%三%';
will put the names of "Zhang San", "Three-legged Cat", "Tang Sanzang" and so on. Find it;

2. _ represents any single character. Statement:
SELECT * FROM user WHERE name LIKE ';_三_';
Only find "Tang Sanzang" whose name has three characters and the middle one is "三";
SELECT * FROM user WHERE name LIKE ';三__';
Only find "three-legged cat" whose name has three characters and the first character is "三";

3. [ ] means One of the characters listed in parentheses (similar to a regular expression). Statement:
SELECT * FROM user WHERE name LIKE ';[张李王]三';
will find "Zhang San", "Li San", "Wang San" (instead of "Zhang Li Wang San" ");

If [ ] contains a series of characters (01234, abcde, etc.), it can be abbreviated as "0-4", "a-e"
SELECT * FROM user WHERE name LIKE '; Old[1-9]';
will find "old1", "old2",...,"old9";
If you want to find the "-" character, please put it first:' ;张三[-1-9]';

4. [^ ] represents a single character not listed in brackets. Statement:
SELECT * FROM user WHERE name LIKE ';[^Zhang Liwang]三';
will find "Zhao San", "Sun" who are not surnamed "Zhang", "Li", "Wang" Three" and so on;
SELECT * FROM user WHERE name LIKE ';Old[^1-4]';
will exclude "Old1" to "Old4" and search for "Old5", "Old6", ..., "Old 9".

! The last thing is the point!
Due to wildcards, our query statement for special characters "%", "_", "[", "';" cannot be implemented normally. However, the special characters can be queried normally by enclosing them with "[ ]" . Based on this, we write the following function:

function sqlencode(str)
str=replace(str,"';","';';")
str=replace(str," [","[[]") ';This sentence must come first
str=replace(str,"_","[_]")
str=replace(str,"%", "[%]")
sqlencode=str
end function

Recommended tutorial: mysql video tutorial

The above is the detailed content of mysql regular matching fuzzy method to query a certain field. For more information, please follow other related articles on the PHP Chinese website!