The MySQL LIKE operator helps us search for specified patterns in a column. It is useful when we need to find records that match specific patterns, like names starting with a certain letter or containing a specific word. In this article, we will cover the concept, syntax, and examples of using the LIKE operator.

MySQL LIKE Operator

The LIKE operator is used in a WHERE clause to look for a pattern in a column. There are two main wildcards you can use:

• %: Matches zero or more characters.
• _: Matches exactly one character.

Syntax:

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

Where,

• column1, column2, …: The columns you want to retrieve.
• table_name: The table from which you’re getting data.
• columnN: The column to search.
• pattern: The pattern you’re looking for.

Demo MySQL Database

To get a good understanding of the MySQL LIKE operator, we’ll first create a sample database and table, then run some queries to see how the LIKE operator works.

Create employee table:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    position VARCHAR(255),
    salary DECIMAL(10, 2),
    location VARCHAR(255)
);

Insert sample data:

INSERT INTO employees (name, position, salary, location) VALUES
('Amit', 'Manager', 60000.00, 'Mumbai'),
('Ganesh', 'Developer', 55000.00, 'Bangalore'),
('Gaurav', 'Designer', 60000.00, 'Hyderabad'),
('Yuvraj', 'Developer', 52000.00, 'Pune'),
('Divya', 'Manager', 62000.00, 'Delhi'),
('Asmita', 'Tester', 48000.00, 'Pune'),
('Farhan', 'Developer', 51000.00, 'Kolkata'),
('Gita', 'Designer', 50000.00, 'Chennai'),
('Harish', 'Manager', 60000.00, 'Mumbai'),
('Akash', 'Tester', 47000.00, 'Bangalore');

Output:

Output

Examples using the LIKE Operator:

Example 1: Search for Names Starting with ‘A’

SELECT * FROM employees
WHERE name LIKE 'A%';

Output:

Output

Explanation: This query finds all employees whose names start with ‘A‘.

Example 2: Search for Names Ending with ‘h’

SELECT * FROM employees
WHERE name LIKE '%h';

Output:

Output

Explanation: This query finds all employees whose names end with ‘n‘.

Example 3: Search for Names Containing ‘ar’

SELECT * FROM employees
WHERE name LIKE '%ar%';

Output:

Output

Explanation: This query finds all employees whose names contain ‘ar‘.

Example 4: Search for Names with ‘i’ as the Second Character

SELECT * FROM employees
WHERE name LIKE '_i%';

Output:

Output

Explanation: This query finds all employees whose names have ‘i‘ as the second character. It will return:

Conclusion

The MySQL LIKE operator is a powerful tool for pattern matching in SQL queries. It allows you to search for records that match specific criteria using the ‘%‘ and ‘_‘ wildcards. This can be especially useful for finding records based on partial matches, such as names starting with a certain letter or containing specific sequences of characters, enhancing your data retrieval capabilities.

FAQs on MySQL LIKE Operator

What is the purpose of the MySQL LIKE operator?

The LIKE operator is used to search for a specified pattern in a column, making it useful for finding records that match specific criteria.

What are the wildcards used with the LIKE operator?

The two main wildcards are % (matches zero or more characters) and _ (matches exactly one character).

Can the LIKE operator be used with numeric values?

The LIKE operator is generally used with string data types. For numeric values, you typically use other comparison operators.

How do you search for a pattern that includes a wildcard character?

To search for a pattern that includes a wildcard character, you need to escape the wildcard character using the backslash (\). For example, to search for a string that contains %, you would use LIKE '%\%%'.