MySQL LIKE operator

Last update on May 24 2024 07:47:52 (UTC/GMT +8 hours)

LIKE operator

MySQL LIKE operator checks whether a specific character string matches a specified pattern.

This function is useful in -

It allows for pattern matching within strings. This is crucial for searching and filtering data based on specific patterns or characters.
The % and _ are wildcards that can represent any number of characters or a single character, respectively.
It allows for complex search patterns by combining multiple wildcards and characters.
LIKE is commonly used in WHERE clauses to filter rows based on specific patterns or conditions, making it a crucial part of data retrieval.
In natural language processing and text mining, LIKE is invaluable for extracting relevant information from unstructured text.
By combining LIKE with LOWER() or UPPER() functions, you can perform case-insensitive searches, which is particularly useful when dealing with user-generated data.
This is beneficial when you want to find similar strings, even if they're not identical.

Syntax:

expr LIKE pat [ESCAPE 'escape_char']

Pattern matching using SQL simple regular expression comparison. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL.
The pattern need not be a literal string. For example, it can be specified as a string expression or table column.
Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator.
LIKE operator uses WILDCARDS (i.e. %, _) to match the pattern. This is very useful to check whether a particular character or string is present in the records.

% is used to match any number of characters, even zero characters.
_ is used to match exactly one character.

To test for literal instances of a wildcard character, precede it by the escape character. If you do not specify the ESCAPE character, “\” is assumed.
\% is used to match one "%" character.
\_ Matches one "_" character

MySQL Version: 8.0

Example: MySQL LIKE operator

The following MySQL statement scans the whole author table to find any author name which has a first name starting with character ‘W’ followed by any characters.

Sample table: author

Code:


-- Select the columns 'aut_name' and 'country' from the 'author' table
SELECT aut_name, country
FROM author 
-- Return only those rows where 'aut_name' starts with 'W'
WHERE aut_name LIKE 'W%';

Explanation:

The query retrieves specific columns (aut_name and country) from the author table.

It uses the SELECT statement to specify the columns to be fetched.

The FROM clause indicates the table (author) from which to retrieve the data.

The WHERE clause filters the results to include only those rows where the author's name (aut_name) starts with the letter 'W'.

The pattern LIKE 'W%' matches any string that begins with 'W', with % representing any number of characters following 'W'.

Output:

mysql> mysql> SELECT aut_name, country          
    -> FROM author 
    -> WHERE aut_name LIKE 'W%';
+-----------------+---------+
| aut_name        | country |
+-----------------+---------+
| William Norton  | UK      | 
| William Maugham | Canada  | 
| William Anthony | UK      | 
+-----------------+---------+
3 rows in set (0.05 sec)

Relational Algebra Expression:

Relational Algebra Tree:

Example: MySQL LIKE operator matching to end

The following MySQL statement scans the whole author table to find any author which have the name ended with ‘on’ string.

Sample table: author

Code:


-- Select the columns 'aut_name' and 'country' from the 'author' table
SELECT aut_name, country
FROM author     
-- Return only those rows where 'aut_name' ends with 'on'
WHERE aut_name LIKE '%on';

Explanation:

The query retrieves specific columns (aut_name and country) from the author table.

It uses the SELECT statement to specify the columns to be fetched.

The FROM clause indicates the table (author) from which to retrieve the data.

The WHERE clause filters the results to include only those rows where the author's name (aut_name) ends with the string 'on'.

The pattern LIKE '%on' matches any string that ends with 'on', with % representing any number of characters preceding 'on'.

Output:

mysql> SELECT aut_name, country 
    -> FROM author     
    -> WHERE aut_name LIKE '%on';
+----------------+---------+
| aut_name       | country |
+----------------+---------+
| William Norton | UK      | 
| Thomas Merton  | USA     | 
| Piers Gibson   | UK      | 
| Joseph Milton  | USA     | 
+----------------+---------+
4 rows in set (0.00 sec)

Relational Algebra Expression:

Relational Algebra Tree:

Example : MySQL LIKE operator matching within the string

The following MySQL statement scans the whole author table to find any author which have a string ‘an’ in his name. Name of the author is stored in aut_name column.

Sample table: author

Code:


-- Select the columns 'aut_name' and 'country' from the 'author' table
SELECT aut_name, country
-- Specify the table to retrieve the data from, which is 'author'
FROM author
-- Apply a filter to return only those rows where 'aut_name' contains the string 'an'
WHERE aut_name LIKE '%an%';

Explanation:

The query retrieves specific columns (aut_name and country) from the author table.

It uses the SELECT statement to specify the columns to be fetched.

The FROM clause indicates the table (author) from which to retrieve the data.

The WHERE clause filters the results to include only those rows where the author's name (aut_name) contains the substring 'an'.

The pattern LIKE '%an%' matches any string that contains 'an' anywhere within the name, with % representing any number of characters before and after 'an'.

Output:

mysql> SELECT aut_name, country 
    -> FROM author
    -> WHERE aut_name LIKE '%an%';
+----------------------+-----------+
| aut_name             | country   |
+----------------------+-----------+
| William Anthony      | UK        | 
| S.B.Swaminathan      | India     | 
| Thomas Morgan        | Germany   | 
| John Betjeman Hunter | Australia | 
| Evan Hayek           | Canada    | 
| Butler Andre         | USA       | 
+----------------------+-----------+
6 rows in set (0.00 sec)

Relational Algebra Expression:

Relational Algebra Tree:

Example : MySQL LIKE operator matching a specified string

The following MySQL statement searches all authors whose home city are such as ‘London’, ’Landon’ etc. the underscore wildcard is used to mention single character.

Sample table: author

Code:


-- Select the columns 'aut_name', 'country', and 'home_city' from the 'author' table
SELECT aut_name, country, home_city
-- Specify the table to retrieve the data from, which is 'author'
FROM author        
-- Apply a filter to return only those rows where 'home_city' matches the pattern 'L_n_on'
WHERE home_city LIKE 'L_n_on';

Explanation:

The query retrieves specific columns (aut_name, country, and home_city) from the author table.

It uses the SELECT statement to specify the columns to be fetched.

The FROM clause indicates the table (author) from which to retrieve the data.

The WHERE clause filters the results to include only those rows where the home_city matches the pattern 'L_n_on'.

The pattern LIKE 'L_n_on' matches any string where:

The first character is 'L'.

The second character can be any single character.

The third character is 'n'.

The fourth and fifth characters are 'on'.

Output:

mysql> SELECT aut_name, country,home_city       
    -> FROM author        
    -> WHERE home_city LIKE 'L_n_on';
+--------------+---------+-----------+
| aut_name     | country | home_city |
+--------------+---------+-----------+
| Piers Gibson | UK      | London    | 
| C. J. Wilde  | UK      | London    | 
+--------------+---------+-----------+
2 rows in set (0.00 sec)

Relational Algebra Expression:

Relational Algebra Tree:

Example : MySQL LIKE operator matching escape character

To search a wildcard character or a combination of a wildcard character and any other character, the wildcard character must be preceded by an ESCAPE string. In MySQL, the default ESCAPE string is "\". The following MySQL statement returns those records, whose isbn_no contain '_16'.

Sample table: book_mast

code:


-- Select the columns 'book_name', 'isbn_no', 'no_page', and 'book_price' from the 'book_mast' table
SELECT book_name, isbn_no, no_page, book_price
-- Specify the table to retrieve the data from, which is 'book_mast'
FROM book_mast 
-- Apply a filter to return only those rows where 'isbn_no' matches the pattern '%\_16%'
WHERE isbn_no LIKE '%\_16%';

Explanation:

The query retrieves specific columns (book_name, isbn_no, no_page, and book_price) from the book_mast table.

It uses the SELECT statement to specify the columns to be fetched.

The FROM clause indicates the table (book_mast) from which to retrieve the data.

The WHERE clause filters the results to include only those rows where the isbn_no matches the pattern '%\_16%'.

The pattern LIKE '%\_16%' matches any string where:

The string contains '_16' preceded by any number of characters.

The underscore character ('_' ) is used as an escape character to match the literal underscore character.

The % represents any number of characters before and after the string '_16'.

Output:

mysql> SELECT book_name,isbn_no,no_page,book_price 
    -> FROM book_mast 
    -> WHERE isbn_no LIKE '%\_16%';
+---------------------------------+-------------+---------+------------+
| book_name                       | isbn_no     | no_page | book_price |
+---------------------------------+-------------+---------+------------+
| Networks and Telecommunications | 00009790_16 |      95 |      45.00 | 
+---------------------------------+-------------+---------+------------+
1 row in set (0.00 sec)

Relational Algebra Expression:

Relational Algebra Tree:

Example : MySQL LIKE operator matching beginning and ending string

Wildcards can also be used in the middle of a search pattern. The following MySQL statement will find all authors whose name begin with a ‘t’ and end with an ‘n’.

Sample table: author

Code:


-- Select the columns 'aut_name' and 'country' from the 'author' table
SELECT aut_name, country
-- Specify the table to retrieve the data from, which is 'author'
FROM author         
-- Apply a filter to return only those rows where 'aut_name' starts with 't' and ends with 'n'
WHERE aut_name LIKE 't%n';

Explanation:

The query retrieves specific columns (aut_name and country) from the author table.

It uses the SELECT statement to specify the columns to be fetched.

The FROM clause indicates the table (author) from which to retrieve the data.

The WHERE clause filters the results to include only those rows where the aut_name matches the pattern 't%n'.

The pattern LIKE 't%n' matches any string where:

The string starts with 't'.

The '%' represents any number of characters between 't' and 'n'.

The string ends with 'n'.

Output:

mysql> SELECT aut_name, country
    -> FROM author         
    -> WHERE aut_name LIKE 't%n';
+---------------+---------+
| aut_name      | country |
+---------------+---------+
| Thomas Morgan | Germany | 
| Thomas Merton | USA     | 
+---------------+---------+
2 rows in set (0.00 sec)

Relational Algebra Expression:

Relational Algebra Tree:

Slideshow of MySQL Comparison Function and Operators

Previous: LESS THAN OPERATOR(<)
Next: NOT BETWEEN AND