Introduction to fuzzy queries in SQL

Source: Internet
Author: User
When you perform a database query, you can perform both full and fuzzy queries. The general fuzzy statement is as follows: SELECT field FROM table WHERE a field Like condition. About the condition, SQL provides four matching modes: %: represents any 0 or multiple characters. It can match any type and length of characters. In some cases, if it is Chinese, use two percent signs (%

When you perform a database query, you can perform both full and fuzzy queries. The general fuzzy statement is as follows: SELECT field FROM table WHERE a field Like condition. About the condition, SQL provides four matching modes: %: represents any 0 or multiple characters. It can match any type and length of characters. In some cases, if it is Chinese, use two percent signs (%

When you perform a database query, you can perform both full and fuzzy queries.

General Fuzzy statements are as follows:

SELECT field FROM table WHERE a field Like Condition

SQL provides four matching modes for conditions:

  1. %: Represents any 0 or multiple characters. It can match any type and length of characters. In some cases, if it is Chinese, use two percent signs (%.
  2. For example, SELECT * FROM [user] WHERE u_name LIKE '% 3%'

    We will find all records with "3", such as "3 Zhang", "3 Zhang", "3 Zhang Mao", and "3 Tang sanzang.

    In addition, if you need to find records with "three" and "cat" in u_name, use the and condition.

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

    If SELECT * FROM [user] WHERE u_name LIKE '% 3% cat %' is used'

    Although three-legged cats can be searched, three-legged cats cannot be searched ".

  3. _: Represents any single character. Matches any character. It is used to limit the character length of an expression:
  4. For example, SELECT * FROM [user] WHERE u_name LIKE '_ 3 _'

    Only find that the u_name such as "Tang sanzang" is three characters and the middle word is "three;

    For example, SELECT * FROM [user] WHERE u_name LIKE 'three __';

    Find out that the name of the "three-legged cat" is three characters and the first word is "three;

  5. []: Represents one of the characters listed in parentheses (similar to a regular expression ). Specifies a character, string, or range. The matching object must be one of them.
  6. For example, SELECT * FROM [user] WHERE u_name LIKE '[Zhang Li Wang] 3'

    We will find "Zhang San", "Li San", and "Wang San" (rather than "Zhang Li Wang San ");

    For example, if [] contains a series of characters (01234, abcde, etc.), it can be slightly written as "0-4" or "a-e"

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

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

  7. [^]: A single character not listed in parentheses. The value is the same as [], but it requires that the matched object be any character other than the specified character.
  8. For example, SELECT * FROM [user] WHERE u_name LIKE '[^ Zhang Li Wang] 3'

    We will find "Zhao San" and "Sun San" without the surname "Zhang", "Li", and "Wang;

    SELECT * FROM [user] WHERE u_name LIKE 'Old [^ 1-4] ';

    From "Old 1" to "old 4", search for "old 5", "old 6 ",......

  9. When the query content contains wildcards
  10. The special characters "%", "_", and "[" cannot be properly queried due to wildcards, when special characters are included in "[]", they can be queried normally. Then, write the following functions:

    Function sqlencode (str) str = replace (str, "[", "[[]") 'must be at the beginning of str = replace (str ,"_", "[_]") str = replace (str, "%", "[%]") sqlencode = strend function

    The string to be queried can be processed by this function before query.

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: info-contact@alibabacloud.com 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.