MySQL LIKE Operator

MySQL LIKE Operator

The MySQL LIKE operator is used to search for a specified pattern in a column. This operator is essential for querying data that matches a particular pattern in SQL queries.

Syntax

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

The LIKE operator has the following components:

  • column1, column2, ...: The columns to be retrieved.
  • table_name: The name of the table from which to retrieve the data.
  • column1 LIKE pattern: The condition to filter the records where column1 matches the specified pattern.

Example MySQL LIKE Operator

Let's look at some examples of the MySQL LIKE operator:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    email VARCHAR(100)
);

This query creates a table named employees with columns for id, first_name, last_name, department, and email.

MySQL CREATE TABLE

Step 3: Inserting Initial Rows

Insert some initial rows into the table:

INSERT INTO employees (first_name, last_name, department, email)
VALUES ('John', 'Doe', 'HR', 'john.doe@example.com'),
       ('Jane', 'Smith', 'IT', 'jane.smith@example.com'),
       ('Jim', 'Brown', 'IT', 'jim.brown@example.com'),
       ('Jake', 'White', 'HR', 'jake.white@example.com'),
       ('Jill', 'Green', 'Marketing', 'jill.green@example.com');

This query inserts five rows into the employees table.

MySQL INSERT INTO TABLE

Step 4: Using LIKE with WHERE Clause

Use the LIKE operator to filter records based on a pattern:

SELECT * 
FROM employees 
WHERE first_name LIKE 'J%';

This query retrieves all columns from the employees table where the first_name starts with 'J'.

MySQL LIKE WITH WHERE CLAUSE

Step 5: Using LIKE with Wildcards

Use the LIKE operator with wildcards:

SELECT * 
FROM employees 
WHERE email LIKE '%example.com';

This query retrieves all columns from the employees table where the email ends with 'example.com'.

MySQL LIKE WITH WILDCARDS

Step 6: Using LIKE with Multiple Patterns

Use the LIKE operator with multiple patterns:

SELECT * 
FROM employees 
WHERE first_name LIKE 'J%' AND last_name LIKE 'D%';

This query retrieves all columns from the employees table where the first_name starts with 'J' and the last_name starts with 'D'.

MySQL LIKE WITH MULTIPLE PATTERNS

Conclusion

The MySQL LIKE operator is a powerful tool for searching for a specified pattern in a column. Understanding how to use the LIKE operator is essential for effective data querying and analysis in MySQL.