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

Learn how to do pattern matching on strings.

1. how to use the LIKE operator

--Search for employee names containing the letter A

SELECT	ENAME
FROM	EMP
WHERE	ENAME 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.

Practice (1/1) Question

Display the ENAME and SAL (salary) from the EMP Table with 'A' in ENAME (employee name).

Practice (1/1) Answer

SELECT	ENAME,SAL
FROM	EMP
WHERE	ENAME LIKE '%A%'

2.past question of Pattern search (LIKE)

Beginner System Administrator AM Q28, Fall 2001

Question
Answer and Explanation

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

EmpNo	fullname	sectcode	extel
S02	Tanaka sanro	K001	1001
S05	Sato kosan	K003	1003
S15	Morisan keiko	K022	3022
S20	Suzuki sanro	K105	1105

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

A	fullname = 'san'
B	fullname = '%san%'
C	fullname LIKE '%san%'
D	fullname 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)].