mysql regular matching fuzzy query for a certain field

silencement
Release: 2023-04-08 10:54:02
forward
3328 people have browsed it

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!

Related labels:
source:www.liqingbo.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!