Teaches you how to use the select like statement in SQL

Like statements in SQL have irreplaceable important role, the following will introduce you to the SQL statement in the details of the use of a, I hope you can help.

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.

a:% an arbitrary string containing 0 or more characters:

1. Like ' mc% ' will search for all strings (such as McBadden) that begin with the letter Mc.

2. Like '%inger ' will search for all strings ending with the letter inger (such as Ringer, Stringer).

3. Like '%en% ' will search all strings containing the letter en in any location (e.g. Bennet, Green, McBadden).

B:_ (underline)

Any single character: Like ' _heryl ' will search for all six-letter names (such as Cheryl, Sheryl) ending with the letter heryl.

c:[] Specifies any single character in the range ([A-f]) or collection ([abcdef]):

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 ' will search for all names (such as Ringer) ending with the string inger, starting with any single letter from M to Z.

d:[^] Any single character that does not belong to the specified range ([a-f]) or collection ([abcdef]):

Like ' m[^c]% ' begins the search with the letter M, and the second letter is not all names of C (such as Macfeather).

e:* it with a wildcard character in a DOS command that represents multiple characters:

C*c represents multiple characters such as CC,CBC,CBC,CABDFEC.

F:? Same as in DOS command? wildcard characters, which represent a single character:

B?b represents BRB,BFB, etc.

g:#, the difference is that the generation can only represent a single number.

K#k represents k1k,k8k,k0k.

F:[!] Excluding it represents only a single character

Let's take a look at the following example:

Example 1, the Query Name field contains the word "Ming". SELECT * FROM table1 the where name like '% Ming% '

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 is like ' * Ming * '

SELECT * FROM table1 the where name like '% Ming% '

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.

Teaches you how to use the select like statement in SQL