SQL statement select like

SQL statement select like

 

The syntax format of the like statement is: Select * from table name where field name like corresponding value (substring), which is mainly for struct fields, it is used to retrieve corresponding substrings in a struct field column.

 

Assume that a database has a table named Table1, and Table1 has two fields, namely name and sex, which are full of metadata data. Now we want to query records starting with "Zhang" in the name field. The statement is as follows:

 

Select * From Table1 where name like "sheet *"

 

To query records ending with "sheets", the statement is as follows:

 

Select * From Table1 where name like "* Sheets"

The wildcard "*" is used here. It can be said that the like statement is inseparable from the wildcard. The following describes the wildcard characters in detail.

 

Matching type

Mode

Examples and representative values

Description

Multiple characters

*

C * C stands for CC, CBC, CBC, cabdfec, etc.

It is equivalent to a wildcard in the doscommand and represents multiple characters.

Multiple characters

%

% C % Represents agdcagd, etc.

This method is used in many programs, mainly for queries containing substrings.

Special characters

 

A

A Represents a *

Replace *

Single Character

?

B? B Represents BRB, BFB, etc.

Is the same as that in the doscommand? Wildcard, representing a single character

Single digit

#

K # k Represents k1k, k8k, k0k

The same is roughly the same as that of a single number.

Character range

-

[A-Z] represents any of the 26 letters from A to Z

Specify any one in a range

Continue

Exclude

[! Character]

[! A-Z] indicates 9, 0, %, *, etc.

It only represents a single character

Number exclusion

[! Number]

[! 0-9] indicates a, B, c, d, etc.

Same as above

Combination Type

Character [range type] Character

CC [! A-d] # stands for CCF # and so on

It can be used in combination with other methods

Assume that Table 1 contains the following records:

Name sex

Zhang Xiaoming male

Li tomorrow male

Li a tianfemale

Wang 5, five male

Wang qingwu male

The following is an example:

Example 1: the query name field contains the word "Ming.

Select * From Table1 where name like '% Ming %'

Example 2: The Name field starts with "Li.

Select * From Table1 where name like 'Lee *'

Example 3: the query name field contains numbers.

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 numbers.

Select * From Table1 where name like '% [! 0-9] %'

The values listed in the preceding example are obvious. But here, it is important to note the difference between the wildcard "*" and "%.

Many may ask, why do I use "%" instead of "*" When individually representing all characters in the preceding query? First, let's take a look at the results of the following example:

Select * From Table1 where name like '* Ming *'

Select * From Table1 where name like '% Ming %'

We can see that the previous statement lists all records, and the next record lists the records whose name field contains "Ming". Therefore, we recommend that you use "%" instead of "*" when using a struct field to contain a substring. When using "*", you only start or end, but cannot replace any character with "*" at both ends.