SQL LIKE Operator

Introduction

In this chapter, we will learn how to use the LIKE operator in SQL. The LIKE operator is used to search for a specified pattern in a column. This chapter will cover the syntax, usage, and provide examples to help you understand how to use the LIKE operator effectively.

What is the LIKE Operator?

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It is often used with wildcard characters to match parts of the column values. The most common wildcards are the percent sign (%) and the underscore (_).

Wildcard Characters

• %: Represents zero, one, or multiple characters.
• _: Represents a single character.

Syntax for LIKE

Basic Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

• column1, column2, ...: The columns you want to retrieve.
• table_name: The name of the table from which you want to retrieve data.
• column_name: The column to be searched.
• pattern: The pattern to search for, which can include wildcard characters.

Example

Assume we have a table named employees:

SELECT first_name, last_name, email
FROM employees
WHERE first_name LIKE 'R%';

This command retrieves all employees whose first_name starts with the letter 'R'.

Step-by-Step Example

1. Create a Sample Tables

First, we will create a sample tables - employees and departments.

CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);

CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
department_id INT,
salary DECIMAL(10, 2)
);

2. Insert Sample Data

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');


INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ramesh', 'Kumar', 'ramesh.kumar2@example.com', 3, 50000);

3. Using the LIKE Operator

To retrieve employees whose first_name starts with 'R':

SELECT first_name, last_name, email
FROM employees
WHERE first_name LIKE 'R%';

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com
Ramesh	Kumar	ramesh.kumar2@example.com

4. Using LIKE with %

The percent sign (%) represents zero, one, or multiple characters.

Example

To retrieve employees whose email ends with 'example.com':

SELECT first_name, last_name, email
FROM employees
WHERE email LIKE '%@example.com';

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com
Sita	Patel	sita.patel@example.com
Arjun	Singh	arjun.singh@example.com
Priya	Sharma	priya.sharma@example.com
Ramesh	Kumar	ramesh.kumar2@example.com

5. Using LIKE with _

The underscore (_) represents a single character.

Example

To retrieve employees whose first_name has 'a' as the second character:

SELECT first_name, last_name, email
FROM employees
WHERE first_name LIKE '_a%';

Output

first_name	last_name	email
Sita	Patel	sita.patel@example.com
Ramesh	Kumar	ramesh.kumar@example.com
Ramesh	Kumar	ramesh.kumar2@example.com

Using NOT LIKE

The NOT LIKE operator is used to exclude rows that match a specified pattern.

Example

To retrieve employees whose first_name does not start with 'R':

SELECT first_name, last_name, email
FROM employees
WHERE first_name NOT LIKE 'R%';

Output

first_name	last_name	email
Sita	Patel	sita.patel@example.com
Arjun	Singh	arjun.singh@example.com
Priya	Sharma	priya.sharma@example.com

Using LIKE with ESCAPE

If you need to search for a literal percent sign or underscore, you can use the ESCAPE keyword.

Example

Assume we have a table with a column containing special characters:

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('John', 'Doe', 'john.doe@example.com', 4, 70000),
('Jane', 'Doe%', 'jane.doe%example.com', 4, 72000),
('James', 'Smith_', 'james.smith@example.com', 4, 75000);

To retrieve employees whose last_name contains a literal percent sign:

SELECT first_name, last_name, email
FROM employees
WHERE last_name LIKE '%\%%' ESCAPE '\';

Output

first_name	last_name	email
Jane	Doe%	jane.doe%example.com

Conclusion

The LIKE operator is a versatile tool for searching for patterns in SQL queries. This chapter covered the basic syntax, using % and _ wildcards, using NOT LIKE, and using LIKE with ESCAPE to handle special characters. Understanding how to use the LIKE operator effectively will enhance your ability to query and analyze your database data.