Content Like statements have a crucial role in SQL Structured Query language.
The syntax format for the LIKE statement is: SELECT * from table name where field name like corresponding value (substring), it is mainly for the character field, its function is to retrieve the string containing the corresponding substring in a character field column.
Suppose there is a database with a table table1, there are two fields in Table1, respectively, name and sex are all character data. Now we're going to look at the records that begin with the word "Zhang" in the Name field, with the following statement:
SELECT * FROM table1 where name is like "Zhang *"
If you want to query for records that end with "Zhang", the statement is as follows:
SELECT * FROM table1 where name is like "* Zhang"
The wildcard character "*" is used here, so you can say that the like statement is inseparable from a wildcard. Let's take a look at the wildcard character in detail below.
Match type |
Mode |
Examples and representative values |
Description |
Multiple characters |
* |
C*c represents CC,CBC,CBC,CABDFEC, etc. |
It is the same as a wildcard character in a DOS command that represents multiple characters. |
Multiple characters |
% |
%c% represents AGDCAGD, etc. |
This method is used in many programs, mainly the query contains substrings. |
Special characters |
[*] |
A[*]a Representative A*a |
instead |
Single-character |
? |
B?b represents BRB,BFB, etc. |
Same as in DOS command? Wildcards, which 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 of the 26 letters from A to Z |
Specify any one of the ranges |
Continue on
| Exclude |
[! character] |
[!a-z] stands for 9,0,%,* etc. |
It represents only a single character |
| Digital exclusions |
[! number] |
[!0-9] stands for a,b,c,d etc. |
Ditto |
| Combination type |
character [range type] Character |
CC[!A-D] #代表ccF # etc |
Can be combined with several other ways |
Suppose the following records are in table table1:
Name Sex
Zhang Xiaoming Male
Lee Tomorrow Man
Lee A-day female
Wang 55 Male
King Qingyue Five Male
Let's take a look at the following example:
Example 1, the Query Name field contains the word "Ming".
SELECT * FROM table1 where name like'%% '
Example 2, the Query Name field begins with the word "Li".
SELECT * FROM table1 where name like' Lee * '
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 focus on the difference between a wildcard "*" and a "%".
Many 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 example to see what happens:
SELECT * FROM table1 where name like' * Ming * '
SELECT * FROM table1 where name like'%% '
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 it is best to use "%" instead of "*" when we make a query with a substring in the character field, but only at the beginning or only at the end when using "*". Instead of "*" in place of any character at both ends. |
