Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞
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.
Examples and representative values
C*C representative CC,CBC,CBC,CABDFEC, etc.
It is the same as a wildcard character in a DOS command that represents multiple characters.
%c% representative AGDCAGD, etc.
This method is used in many programs, mainly the query contains substrings.
A[*]a Representative A*a
B?B representative BRB,BFB, etc.
Same as in DOS commands? Wildcard characters that represent a single character
K#k Representative k1k,k8k,k0k
In general, the difference is that the generation can only represent a single number.
-[a-z] represents any one of the 26 letters A through Z that specifies any one of the ranges
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:
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
This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or
reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or
complaint, to firstname.lastname@example.org. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.
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.