MySQL LIKE operator

LIKE operator

MySQL LIKE operator along with WILDCARDS finds a string of a specified pattern within another string.

In a more technical note, LIKE operator does pattern matching using simple regular expression comparison.

This is a table which describes the wildcards used with MySQL LIKE operator -

This function is useful in -

• Pattern matching: It allows you to find strings that match a specific pattern.
• Wildcard characters: Within the LIKE pattern, wildcard characters (_, %) can match different parts of the string.
• Data filtering: LIKE operators are commonly used in the WHERE clause to filter data.

Syntax:

LIKE pat

Argument:

MySQL Version: 8.0

Example of MySQL LIKE operator with wildcard (%) matching from the beginning

The following MySQL statement will return those rows from the table author in which the name of the author starts with the character ‘W’.

Code:

SELECT * 
FROM author 
WHERE aut_name LIKE 'W%';

Relational Algebra Expression:

Relational Algebra Tree:

Sample table: author

Output:

mysql> SELECT * 
    -> FROM author 
    -> WHERE aut_name LIKE 'W%';
+--------+-----------------+---------+-----------+
| aut_id | aut_name        | country | home_city |
+--------+-----------------+---------+-----------+
| AUT001 | William Norton  | UK      | Cambridge | 
| AUT002 | William Maugham | Canada  | Toronto   | 
| AUT003 | William Anthony | UK      | Leeds     | 
+--------+-----------------+---------+-----------+
3 rows in set (0.04 sec)

Example of MySQL LIKE operator with wildcard (%) matching from the end

The following MySQL statement will return those rows from the table author in which the name of the author ends with the substring ‘on’.

Code:

SELECT * FROM author 
WHERE aut_name LIKE '%on';

Relational Algebra Expression:

Relational Algebra Tree:

Sample table: author

Output:

mysql> SELECT * 
    -> FROM author 
    -> WHERE aut_name LIKE '%on';
+--------+----------------+---------+-----------+
| aut_id | aut_name       | country | home_city |
+--------+----------------+---------+-----------+
| AUT001 | William Norton | UK      | Cambridge | 
| AUT006 | Thomas Merton  | USA     | New York  | 
| AUT007 | Piers Gibson   | UK      | London    | 
| AUT010 | Joseph Milton  | USA     | Houston   | 
+--------+----------------+---------+-----------+
4 rows in set (0.00 sec)

Example of MySQL LIKE operator with wildcard (%) matching within the string

The following MySQL statement will return those rows from the table author in which the name of the author contains ‘k’.

Code:

SELECT * FROM author 
WHERE aut_name LIKE '%k%';

Relational Algebra Expression:

Relational Algebra Tree:

Sample table: author

Output:

mysql> SELECT * 
    -> FROM author 
    -> WHERE aut_name LIKE '%k%';
+--------+---------------+---------+-----------+
| aut_id | aut_name      | country | home_city |
+--------+---------------+---------+-----------+
| AUT008 | Nikolai Dewey | USA     | Atlanta   | 
| AUT012 | Evan Hayek    | Canada  | Vancouver | 
+--------+---------------+---------+-----------+
2 rows in set (0.00 sec)

Using ESCAPE to search for wildcard characters

In this example, the LIKE operator is used to find a product with the name '5% Discount'. Since % is a wildcard character, we use the ESCAPE keyword to escape the % symbol and treat it as a regular character.

Code:

SELECT * FROM products WHERE product_name LIKE '5|% Discount' ESCAPE '|';

Sample table: products

Output:

id|product_name|
--+------------+
 4|5% Discount |

Example of MySQL LIKE operator with wildcard (_) underscore

The following MySQL statement will return those rows from the table author in which the length of the author’s name is exactly 12 characters. Twelve ‘_’ have been used to indicate 12 characters.

Code:

SELECT * FROM author 
WHERE aut_name LIKE '____________'; 

Relational Algebra Expression:

Relational Algebra Tree:

Sample table: author

Output:

mysql> SELECT *
    -> FROM author 
    -> WHERE aut_name LIKE '____________'; 
+--------+--------------+---------+-----------+
| aut_id | aut_name     | country | home_city |
+--------+--------------+---------+-----------+
| AUT007 | Piers Gibson | UK      | London    | 
| AUT015 | Butler Andre | USA     | Florida   | 
+--------+--------------+---------+-----------+
2 rows in set (0.00 sec)

Video Presentation

All String Functions (Slides presentation)

Previous: LENGTH
Next: LOAD_FILE