Using Like In MySQL for Search Operations Using Pattern

This tutorial is part of the series Learn Basic SQL Queries Using MySQL. In this tutorial, we will discuss SQL queries to perform search operations on the table data in MySQL using the WHERE clause with the LIKE condition.

Like Condition

The LIKE and NOT LIKE conditions can be used to perform search operations on the table data using the specified pattern.

# LIKE - Syntax
SELECT * FROM `table_name` WHERE `column_name` LIKE '<pattern>';

# NOT LIKE - Syntax
SELECT * FROM `table_name` WHERE `column_name` NOT LIKE '<pattern>';

Query Explanation

The LIKE condition can be used to search in a specific column using the specified pattern. We can use the LIKE condition with WHERE to perform the search operations.

We have to specify at least one condition using the WHERE clause for the rows to qualify for deletion else the entire table will be erased. All the rows of the table get deleted without the condition.

We can use the below-mentioned wildcards with the LIKE condition to refine the search results.

Percent (%) - It represents zero, one, or multiple characters

Underscore (_) - It represents a single character

We can also use the ESCAPE keyword to include the wildcards in search results.

Examples

This section provides examples to perform search operations using the LIKE condition with WHERE clause. Use the below-mentioned query to create the user table having the id, first name, last name, and active columns to store user data.

# Create the User Table

CREATE TABLE `enterprise`.`user` (
  `user_id` BIGINT NOT NULL,
  `first_name` VARCHAR(45) ,
  `last_name` VARCHAR(45),
  `active` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`user_id`));

The below-mentioned query can be used to insert data in the user table.

# Insert Rows - All Columns

INSERT INTO `user` ( `user_id`, `first_name`, `last_name`, `active` ) VALUES 
    ( 1, 'John', 'Smith', 1 ),
    ( 2, 'Rick', 'Jones', 1 ),
    ( 3, 'Catherine', 'Ponting', 0 ),
    ( 4, 'Harsh', 'Upadhyay', 1 ),
    ( 5, 'Tajwinder', 'Singh', 0 );

The above-mentioned query will insert 5 rows in the table having the id, first name, last name, and active columns to represent 5 different users.

Now we will use the LIKE condition with the WHERE clause to search either in the first name or last name columns of the user table. It can be done using the WHERE LIKE in MySQL as shown below.

# LIKE - Exact search - Single Condition
SELECT * FROM `user` WHERE `first_name` LIKE 'John';
SELECT * FROM `user` WHERE `first_name` LIKE 'john';

# Result
1	John	Smith	1

# LIKE - Exact search - Multiple Conditions
SELECT * FROM `user` WHERE `first_name` LIKE 'Rick' AND `last_name` LIKE 'Jones';

# Result
2	Rick	Jones	1

# NOT LIKE - Exact search - Single Condition
SELECT * FROM `user` WHERE `first_name` NOT LIKE 'John';

# Result
2	Rick		Jones		1
3	Catherine	Ponting		0
4	Harsh		Upadhyay	1
5	Tajwinder	Singh		0

The above-mentioned queries perform an exact search using WHERE LIKE and the given pattern. The LIKE condition ignores the case while matching with the column values. It provides the same result with uppercase or lowercase.

Now we will use the wildcard to perform the searching as shown below.

# LIKE - % Wildcard search
SELECT * FROM `user` WHERE `first_name` LIKE '%ine';

# Result
3	Catherine	Ponting	0

# LIKE - % Wildcard search
SELECT * FROM `user` WHERE `first_name` LIKE 'Cat%in%';

# Result
3	Catherine	Ponting	0

# LIKE - % Wildcard search
SELECT * FROM `user` WHERE `first_name` LIKE '%in%';

# Result
3	Catherine	Ponting	0
5	Tajwinder	Singh	0

# LIKE - _ Wildcard search
SELECT * FROM `user` WHERE `first_name` LIKE '_ohn';

# Result
1	John	Smith	1

# LIKE - % & _ Wildcard search
SELECT * FROM `user` WHERE `first_name` LIKE '_oh%';

# Result
1	John	Smith	1

The below-mentioned query explains the usage of the ESCAPE keyword with the LIKE condition.

# Insert Rows - All Columns
INSERT INTO `user` ( `user_id`, `first_name`, `last_name`, `active` ) VALUES 
    ( 6, 'Jo%hn', 'Waugh', 1 ),
    ( 7, 'Rick', 'Jones', 1 );

# LIKE - ESCAPE - Exact search
SELECT * FROM `user` WHERE `first_name` LIKE '%#%hn' ESCAPE '#';

# Result
6	Jo%hn	Waugh	1

This is how we can use the LIKE condition with the WHERE clause with and without wildcard to perform search operations using the specified pattern.