MySQL LIKE Operator

Introduction

In this chapter, we will learn about the LIKE operator in MySQL. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. This is particularly useful for filtering data based on partial matches. We will cover the syntax, examples, and important considerations for using the LIKE operator.

Syntax

The basic syntax for the LIKE operator is:

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

• column_name: The column to search for the pattern.
• pattern: The pattern to match, which can include wildcards:
  • %: Represents zero or more characters.
  • _: Represents a single character.

Using LIKE Operator

Example with %

The % wildcard represents zero or more characters.

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

This example retrieves rows where the first_name starts with the letter 'R'.

Example with _

The _ wildcard represents a single character.

SELECT first_name, last_name, email
FROM students
WHERE first_name LIKE 'R_h_l';

This example retrieves rows where the first_name matches the pattern 'R_h_l', such as 'Rahul'.

Example with Combined Wildcards

SELECT first_name, last_name, email
FROM students
WHERE email LIKE '%@example._om';

This example retrieves rows where the email ends with '@example.com' or '@example.aom'.

Using LIKE with NOT

To find rows that do not match a pattern, you can use the NOT keyword with the LIKE operator.

Example

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

This example retrieves rows where the email does not end with '@example.com'.

Full Example

Let's go through a full example where we create a table, insert data into it, and use the LIKE operator to filter records.

1. Create a Database:

CREATE DATABASE school;

2. Select the Database:

USE school;

3. Create a Table:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    enrollment_date DATE
);

4. Insert Data into the Table:

INSERT INTO students (first_name, last_name, email, enrollment_date) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01'),
('Priya', 'Singh', 'priya.singh@example.com', '2023-07-02'),
('Amit', 'Kumar', 'amit.kumar@example.com', '2023-07-03'),
('Neha', 'Verma', 'neha.verma@example.com', '2023-07-04'),
('Sahil', 'Mehta', 'sahil.mehta@example.com', '2023-07-05');

5. Use the LIKE Operator with % Wildcard:

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

Output

first_name	last_name	email
Rahul	Sharma	rahul.sharma@example.com

6. Use the LIKE Operator with _ Wildcard:

SELECT first_name, last_name, email
FROM students
WHERE first_name LIKE 'R_h_l';

Output

first_name	last_name	email
Rahul	Sharma	rahul.sharma@example.com

7. Use the LIKE Operator with Combined Wildcards:

SELECT first_name, last_name, email
FROM students
WHERE email LIKE '%@example._om';

Output

first_name	last_name	email
Rahul	Sharma	rahul.sharma@example.com

8. Use the LIKE Operator with NOT:

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

Output

(empty result set)

Important Considerations

• Case Sensitivity: The LIKE operator is case-insensitive by default in MySQL, but this behavior can be modified with the COLLATE clause.
• Performance: Using the LIKE operator with wildcards, especially % at the beginning of the pattern, can impact performance. Ensure appropriate indexing and optimize queries to improve performance.
• Pattern Matching: Be careful with the patterns you define, as incorrect patterns can lead to unexpected results.

Conclusion

The LIKE operator is used for pattern matching in MySQL queries. This chapter covered how to use the LIKE operator with % and _ wildcards, combining it with other operators like NOT. In the next chapter, we will learn how to use the IN operator to filter data based on a list of values.