Shulou(Shulou.com)05/31 Report--

This article is about how to write SQL fuzzy query statements. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

SQL fuzzy query statements

The syntax of a general fuzzy sentence is as follows:

SELECT FIELD FROM TABLE WHERE A FIELD LIKE CONDITION

SQL provides four matching patterns for conditions:

1, %: represents any 0 or more characters. Matches characters of any type and length. In some cases, if it is Chinese, please use two percent signs ( %%) to indicate it.

SELECT * FROM [user] WHERE u_name LIKE '% three %'

All records with u_name "Zhang San,""Zhang Mao San,""Three-legged Cat,""Tang Sanzang," etc. with "three" will be found. Also, if you need to find records with both "three" and "cat" in u_name, use the and condition

SELECT * FROM [user] WHERE u_name LIKE '% three %' AND u_name LIKE '% cat %'

if used

SELECT * FROM [user] WHERE u_name LIKE '% three % cat %'

Although "three-legged cat" can be searched out,"Zhang Maosan" that meets the conditions cannot be searched out.

2,_: represents any single character. Matches a single arbitrary character, which is often used to limit the character length of expressions:

SELECT * FROM [user] WHERE u_name LIKE '_three_'

Only find "Tang Sanzang" such u_name is three characters and the middle word is "three";

SELECT * FROM [user] WHERE u_name LIKE 'three__';

Find only "three-legged cat" such that the name is three characters and the first word is "three";

[ ]: Indicates one of the characters listed in parentheses (similar to regular expressions). Specifies a character, string, or range that matches any of them.

SELECT * FROM [user] WHERE u_name LIKE '

Will find "Zhang San,""Li San,""Wang San"(instead of "Zhang Li Wang San");

If there is a series of characters (01234, abcde, etc.) in [ ], it can be written as "0-4","a-e"

SELECT * FROM [user] WHERE u_name LIKE 'old [1-9]'

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

4.[^ ]: Indicates a single character that is not listed in parentheses. It has the same value as [], but it requires that the object matched be any character other than the specified character.

SELECT * FROM [user] WHERE u_name LIKE '

Zhao San and Sun San, who are not surnamed "Zhang,""Li" and "Wang," will be found.

SELECT * FROM [user] WHERE u_name LIKE 'old [^1-4 $>';

We will eliminate "old 1" to "old 4" and look for "old 5","old 6",...

5. When the query content contains wildcards

Because of wildcard characters, we can not query the special characters "%,""_,""[" sentences normally, but the special characters enclosed with "[ ]" can be queried normally. From this we write the following function:

function sqlencode(str)str=replace(str,"[","[]") 'This sentence must be preceded by str=replace(str,"_","[_]")str=replace(str,"%","[%]")sqlencode= strand function

Before querying, the string to be searched is processed by this function.

Thank you for reading! About "SQL fuzzy query statement how to write" this article is shared here, I hope the above content can have some help for everyone, so that everyone can learn more knowledge, if you think the article is good, you can share it to let more people see it!

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.