MySQL LIKE Operator

Overview

In this tutorial, we will learn about MySQL LIKE operator. We can use the LIKE operator in the query to check if column values match a specified pattern and search for similar values. We can use this operator when we are not sure of the exact search condition.

Syntax

The general syntax of the LIKE operator is as follows:

SELECT <column_list>
FROM <table_name>
WHERE column LIKE pattern;

The LIKE operator supports two wildcards:

Wildcard

Description

Underscore ( _ )

This wildcard represents exactly one value in the pattern.

Percentage ( % )

This wildcard represents zero or more values in the pattern.

Example

Underscore wildcard

In this example, we will query the Country table from the world MySQL database.

We cannot use the = operator in the WHERE clause to match the partial matches.

mysql> SELECT Count(*) FROM Country
-> WHERE Continent =’___th America’;

+———-+
| Count(*) |
+———-+
| 0 |
+———-+

We have use the LIKE operator with the wildcards. The underscore (_) matches exactly one character. The query
uses three underscores to match any three characters.

mysql> SELECT Count(*) FROM Country
-> WHERE Continent LIKE ‘___th America’;

+———-+
| Count(*) |
+———-+
| 51 |
+———-+

The query matches the continents like ‘North America’ and ‘South America’.

MySQL Like Underscore Wildcard

Percentage wildcard

In this example, we will query the City table from the world MySQL database.We will use the LIKE operator to retrieve rows from the table with the District names that start with ‘New’. We will use the wildcard (%) to match any characters after the string ‘New’ in the district name.

mysql> SELECT * FROM City
-> WHERE District LIKE ‘New %’;

+——+—————+————-+—————–+————+
| ID | Name | CountryCode | District | Population |
+——+—————+————-+—————–+————+
| 130 | Sydney | AUS | New South Wales | 3276207 |
| 137 | Newcastle | AUS | New South Wales | 270324 |
| 138 | Central Coast | AUS | New South Wales | 227657 |
| 139 | Wollongong | AUS | New South Wales | 219761 |
| 148 | Nassau | BHS | New Providence | 172000 |
| 3793 | New York | USA | New York | 8009000 |
| 3827 | Albuquerque | USA | New Mexico | 448607 |
| 3850 | Buffalo | USA | New York | 292648 |
| 3855 | Newark | USA | New Jersey | 273546 |
| 3864 | Jersey City | USA | New Jersey | 240055 |
| 3871 | Rochester | USA | New York | 219773 |
| 3888 | Yonkers | USA | New York | 196086 |
| 3932 | Paterson | USA | New Jersey | 149222 |
| 3935 | Syracuse | USA | New York | 147306 |
| 3978 | Elizabeth | USA | New Jersey | 120568 |
| 4011 | Manchester | USA | New Hampshire | 107006 |
| 4048 | Albany | USA | New York | 93994 |
+——+—————+————-+—————–+————+
17 rows in set (0.01 sec)