Four matching modes of SQL fuzzy query condition

When executing a database query, there is a complete query and a fuzzy query. The general Fuzzy statement format is as follows:

SELECT field from table WHERE a field like condition

In terms of conditions, SQL provides four matching modes: 1, %: Represents any 0 or more characters. Can match any type and length of the character, in some cases, if Chinese, please use two percent sign (%).

For example SELECT * from [user] WHERE u_name like '% three '
will be u_name for "Zhang San", "Zhang Cat Three", "three-legged Cat", "Tang Sanzang" and so on Have "three" records all find out.

For example, if you need to find a record of "three" and "cat" in U_name, use the and condition
SELECT * FROM [user] WHERE u_name like '% three ' and u_name like '% cat% '

For example SELECT * from [user] WHERE u_name like '% cat% '
Although can search out "three feet cat", but can not search out the eligible "Zhang Cat three".

Note :% Three: Indicates a left match. Three: Indicates a right match. % Three: Indicates a fuzzy query.

2. _: Represents any single character. Matches a single arbitrary character, which is commonly used to restrict the expression's character-length statement:

For example SELECT * from [user] WHERE u_name like ' _ Three _ '
Only find "Tang Sanzang", so u_name for three words and the middle of a word is "three";

For example SELECT * from [user] WHERE u_name like ' three __ '
Just find out "three-legged cat" this name is three words and the first word is "three";

3. [ ]: represents one of the characters listed in parentheses (similar to a regular expression). Specifies a character, string, or range that requires matching objects to be any of them.

For example SELECT * from [user] WHERE u_name like ' [Zhang Li Wang] three '
Will find "Zhang San", "Lie Triple", "Wang San" (not "Zhangli Kang");

For example, [] There are a series of characters (01234, ABCDE, etc.) can be slightly written as "0-4", "A-E"
SELECT * FROM [user] WHERE u_name like ' old [1-9] '
Will find "Old 1", "Old 2" 、......、 "Old 9";

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

Oracle 10g and above version usage is:

SELECT * from Flow_user where regexp_like (username, ' [Zhang Wangli] Fly ')

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

For example SELECT * from [user] WHERE u_name like ' [^ Zhang Li Wang] three '
Will find the surname "Zhang", "Li", "Wang" "Zhao Three", "Magozo" and so on;
For example SELECT * from [user] WHERE u_name like ' old [^1-4] '
Will exclude "old 1" to "Old 4", Looking for "old 5", "Old 6" 、......、 "Old 9".

Note: Oracle like does not support regular, you can use regular regexp_like that support like

5. When the contents of the query include a wildcard

* Indicates that all information is found, such as SELECT * from Tbl_user

Because of the wildcard character, which causes us to query the special characters "%", "_", "[" the statement can not be implemented normally, and the special characters with "[]" in the normal query. Thus we write the following function:

function Sqlencode (str) str=replace (str,"[","[[]")'this sentence must be in the firstStr=replace (str,"_","[_]") Str=replace (str,"%","[%]") Sqlencode=strend function

Remark :
The unknown origin string is processed by the function before the query, and it is important to note that when a database is connected to a Web page using this type of query statement:
For example Select * from user Where name like ' old [^1-4] '

The old [^1-4] "'" is to have single quotes!

"Original" http://www.cnblogs.com/LoveSuk/archive/2017/04/06/6672768.html

Four matching modes of SQL fuzzy query condition