Four matching modes of SQL fuzzy query condition

Source: Internet
Author: User

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!


Four matching modes of SQL fuzzy query condition

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.