The usage of like matching in SQL statements: bitsCN.com plays a vital role in the SQL structured query language. 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:
Java code
- Select * from table1 where name like "sheet *"
To query records ending with "sheets", the statement is as follows:
Java code
- 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] indicates any of the 26 letters from a to z specifying a range.
Continue
Exclude [! Character] [! A-z] represents 9, 0, %, *, and so on, it only represents a single character
Number exclusion [! Number] [! 0-9] indicates A, B, C, d, and so on.
Combination type character [range type] character cc [! A-d] # indicates ccF # and can be combined with other methods
Assume that table 1 contains the following records:
Name sex
Zhang Xiaoming male
Li tomorrow male
Li a tiannvwa
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?
Example result:
- 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.
For more information about mysql databases, see http://www.jbxue.com/db/mysql. BitsCN.com
