mysql regular matching fuzzy query for a certain field

When we query a certain field, sometimes we only want to match a certain piece of data. For example, if we want to query all the keywords of this article to see if a certain keyword exists, we often need to Matching, let's explain how to match and query a certain keyword value

The syntax of SQL fuzzy query 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 change the name to "Zhang San", "Three-legged Cat", "Tang Sanzang", etc. Find all characters with "three";

2. _ represents any single character. Statement:

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

Only find "Tang Sanzang" whose name has three characters and the middle character is "三" ;

SELECT * FROM user WHERE name LIKE ';三__';

Only find "three-legged cat" whose name has three characters and the first character is "三" ;

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

SELECT * FROM user WHERE name LIKE ';[张李王]三';

will find "Zhang San", "Li San", "Wang San" (and Not "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 ';老[1-9]';

will find "old 1", "old 2",..., "old 9";

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

4. [^ ] means a single character not listed in brackets. Statement:

SELECT * FROM user WHERE name LIKE ';[^Zhang Liwang]三';

will find "" whose surname is not "Zhang", "Li", or "Wang" Zhao San", "Sun San", etc.;

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

will exclude "老1" to "老4 "Look for "Old 5", "Old 6",..., "Old 9".

! The last thing is the point!

Due to wildcards, our query statements for special characters "%", "_", "[", and "';" cannot be implemented normally, and it is convenient to enclose special characters with "[ ]" Can be queried normally. 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

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