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

Statement
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

Mode

Examples and representative values

Description

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

instead

Given

?

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
E-mail: hoget@hoget.com
URL: http://www.hoget.com
oicq:621150