An in-depth study of like statements in SQL Structured Query language

Source: Internet
Author: User
Tags contains ming sql query range table name
In SQL Structured Query language, like statements have a vital role to play.
The syntax format for like statements is: SELECT * from table name where field name like corresponding value (substring), it is mainly for character fields, it is the role of a character field column to retrieve the containing corresponding substring.
Suppose that there is a table table1 in a database, and there are two fields in Table1, respectively, name and sex, both of which are character-type data. Now we want to query the record in the last Name field that starts with the word "Zhang," as follows:
SELECT * FROM table1 where name like "Zhang *"
If you want to query for records that end with "Zhang," the statement is as follows:
SELECT * FROM table1 where name like "* Zhang"
The wildcard character "*" is used here, and it can be said that the like statement is inseparable from the wildcard character. Let's go through the wildcard characters in more detail below.

Match type


Examples and representative values


Multiple characters


C*C representative CC,CBC,CBC,CABDFEC, etc.

It is the same as a wildcard character in a DOS command that represents multiple characters.

Multiple characters


%c% representative AGDCAGD, etc.

This method is used in many programs, mainly the query contains substrings.

Special characters


A[*]a Representative A*a




B?B representative BRB,BFB, etc.

Same as in DOS commands? Wildcard characters that represent a single character

Single digit


K#k Representative k1k,k8k,k0k

In general, the difference is that the generation can only represent a single number.

Character Range
-[a-z] represents any one of the 26 letters A through Z that specifies any one of the ranges
Continued on
excluding [! Character][!a-z] represents 9,0,%,* and so on it represents only a single character numeric exclusion [! Number][!0-9] represents a,b,c,d equivalent combination type character [range type] character cc[!a-d] #代表ccF # can be combined with several other ways
Assume that the following records are in the table table1:
Name Sex
Zhang Xiaoming Male
Lee Tomorrow Man
Lee a day Girl
Wang 55 men
Qingyue five men
Here are some examples to illustrate:
Example 1, the Query Name field contains the word "Ming".
SELECT * FROM table1 where name is '% '%
Example 2, the Query Name field starts with the word "Lee."
SELECT * FROM table1 where name is like ' Li * '
Example 3, the Query Name field contains a number.
SELECT * FROM table1 where name like '%[0-9]% '
Example 4, the Query name field contains lowercase letters.
SELECT * FROM table1 where name like '%[a-z]% '
Example 5, the Query Name field does not contain a number.
SELECT * FROM table1 where name like '%[!0-9]% '
The above example can list what values to be obvious. But here, we are going to highlight the difference between the wildcard "*" and "%".
A lot of friends ask, why do I use "%" instead of "*" when I have a different representation of all characters in the above query? Let's take a look at the following examples to see what happens separately:
SELECT * FROM table1 where name like ' * ming '
SELECT * FROM table1 where name is '% '%
As you can see, the previous statement lists all the records, and the next record lists the records that contain "Ming" in the Name field, so, when we do a query with a substring in a character field, it's best to use "%" instead of "*", when you use "*" only at the beginning or at the end, Instead of having a "*" in place of any character at both ends.
The above is my humble opinion of like statements, the shortcomings of the friends please correct me.
Hu Guangtao March 8, 2002

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.