MySQL LIKE Operator

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:

Examples using the LIKE Operator:

Example 1: Search for Names Starting with 'A'

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

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:

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:

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:

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.

yuvrajghule281

Follow

Improve

Article Tags :

• MySQL
• Databases