SQL statement select like usage

SQL statement select like usage

In SQL Structured Query Language, like statements play a vital role.

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.

A: %Any string containing zero or multiple characters: 1 And like 'mc % 'search all strings starting with MC (for example, mcbadden ).
2. Like '% Inger' searches all strings ending with the letter Inger (such as ringer and Stringer ).
3. Like '% en %' searches for all strings (such as Bennet, green, and mcbadden) containing the letter en at any position ).
B:_ (Underline)Any single character: Like '_ heryl' searches for names of all six letters ending with heryl, such as Cheryl and Sheryl ).
C: []Specified range([A-f])Or set([Abcdef])Any single character in: 1, Like '[Ck] ARS [EO] n' will search for the following strings: Carsen, Karsen, Carson, and karson (such as Carson ).
2. Like '[M-Z] Inger' searches for all names (such as ringer) ending with string Inger, starting with any single letter from m to Z ).
D:[^]Does not belong to the specified range([A-f])Or set([Abcdef])Any single character: Like'm [^ C] %' searches for all names (such as macfeather) whose names start with M and whose second letter is not C ).
E: * It is equivalent to the wildcard in the doscommand and represents multiple characters: C * c Represents CC, CBC, CBC, cabdfec, and many other characters.
F :? Is the same as that in the doscommand? Wildcard, representing a single character:B? B Represents BRB, BFB, etc.
G: # the same as above. The difference is that the generation can only represent a single number. . K # K indicates k1k, k8k, and k0k.
F: [!] Exclude that it only represents a single character

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.