The usage of like in sql statements is explained in detail

  • 2020-05-27 07:24:42
  • OfStack

In the structured query language SQL, the LIKE statement plays a crucial role.
The syntax format of LIKE statement is: select * from table name where field name like corresponding value (substring), which is mainly for character field, its function is to retrieve the containing corresponding substring in the column of 1 character field.

Suppose there is a database with a table table1, and in table1 there are two fields, name and sex2, which are all character data. Now we will query the record beginning with "zhang" in the name field. The statement is as follows:
select * from table1 where name like "sheet *"
If you want to query a record ending in "zhang", the statement is as follows:
select * from table1 where name like "* sheet"
The wildcard character "*" is used here, so to speak, the like statement is inseparable from the wildcard. Let's take a closer look at wildcards 1.

Matching type
model
Examples and representative values
instructions

More characters
*
c * c cc, cBc cbc, cabdfec, etc
It is the same as the wildcard in the DOS command and represents multiple characters.

More characters
%
%c% stands for agdcagd, etc
This method is used in many programs, primarily queries that contain substrings.

Special characters
[*]
a [*] a * a a representative
Instead of *

A single character
?
b? b stands for brb,bFb, etc
The same as the DOS command? A wildcard character that represents a single character

A single number
#
k # k k1k, k8k k0k
Roughly the same, except that the generation only represents a single number.

Range of characters
- [a-z] represents any one of the 26 letters from a to z, specifying any one of the range
Continue on
Exclude the [! Character] [! a-z] for 9,0,%,*, etc. It only represents a single character
Figure out [! Digital] [! 0-9] represent A, b, C, d etc. Same as above
The combination type character [range type] character cc[! a-d]# represents the combination of ccF# and several other ways

Suppose table table1 has the following records:
name sex
Zhang Xiaoming male
Li Mingtian male
Lee a, having
The king of 55 male
Five male Wang Qing

Let's give an example to illustrate 1:
In example 1, the query name field contains the word "Ming".
select * from table1 where name like '% Ming %'
In example 2, the query name field begins with the word "li".
select * from table1 where name like 'lee *'
For example 3, query for the number in the name field.
select * from table1 where name like '%[0-9]%'
For example 4, look for fields with lowercase letters in name.
select * from table1 where name like '%[a-z]%'
For example 5, query for the name field that does not contain a number.
select * from table1 where name like '%[!0-9]%'

What values can be listed in the above example is obvious. But here, it is important to note the difference between the wildcard character "*" and "%".
Many people will ask, why do I use "%" instead of "*" for all characters in the above query?
Let's take a look at each of the following examples:
select * from table1 where name like * Ming *
select * from table1 where name like

As you can see, the first statement lists all the records, and the second one lists the records that have "Ming" in the name field,
Therefore, it is better to use "%" instead of "*" when making a query with a character field containing 1 substring. When using "*", it is only used at the beginning or at the end, and cannot be replaced by "*" at both ends.


Related articles: