SQL Strategy - A site to master SQL while executing it on the Web
Home >> SQL Strategy - Pattern search (LIKE)

Pattern search (LIKE)

Learn how to do pattern matching on strings.

1. how to use the LIKE operator

--Search for employee names containing the letter A
SELECTENAME
FROMEMP
WHEREENAME LIKE '%A%'
■How to use the LIKE operator

The LIKE operator is used in the WHERE clause. The LIKE operator is used in the WHERE clause to perform pattern matching between the column name and the comparison string by specifying [Column name LIKE Comparison string].

■wildcard

The following two wildcards can be used in the comparison string

%(percent)Any string of 0 or more characters
_(underbar)Any one character

If you want to use % or _ itself as a search string, use the ESCAPE keyword. The following is written and the ESCAPE keyword is used to specify ? but the ? _ is treated as a mere _(A underscore), which is not a wildcard.

WHERE ENAME LIKE '%?_%' ESCAPE '?'

■Negation of the LIKE operator

To negate the LIKE operator, use NOT before the LIKE operator, as in [Column name LIKE comparison string].

WHERE ENAME NOT LIKE '%A%'

■Practice

Now for the exercise, I will give you one Question.


2.past question of Pattern search (LIKE)

Beginner System Administrator AM Q28, Fall 2001
Which word or phrase should be placed in "a" in the SQL statement to create the following "Employee List" table by searching the "Employee" table for employees whose names contain the letter "san"?

Employee
EmpNofullnamesectcodeextel
S02Tanaka sanroK0011001
S05Sato kosanK0031003
S15Morisan keikoK0223022
S20Suzuki sanroK1051105

SELECT EmpNo, fullname, sectcode, extel, FROM Employee
WHERE [ a ]

Afullname = 'san'
Bfullname = '%san%'
Cfullname LIKE '%san%'
Dfullname NOT LIKE '%san%'
Answer

C

Explanation

We are searching for names that contain the letter 'san'. Since such a search can be performed with LIKE, the answer is C.

This completes the [Pattern search (LIKE)].