MySQL: LIKE Condition
This MySQL tutorial explains how to use the MySQL LIKE condition to perform pattern matching with syntax and examples.
Description
The MySQL LIKE condition allows wildcards to be used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement. This allows you to perform pattern matching.
Syntax
The syntax for the LIKE Condition in MySQL is:
expression LIKE pattern [ ESCAPE 'escape_character' ]
Parameters or Arguments
expression
A character expression such as a column or field.
pattern
A character expression that contains pattern matching. The patterns that you can choose from are:
|
Wildcard |
Explanation |
|
% |
Allows you to match any string of any length (including zero length) |
|
_ |
Allows you to match on a single character |
escape_character
Optional. It allows you to test for literal instances of a wildcard character such as % or _. If you do not provide the escape_character, MySQL assumes that "\" is the escape_character.
Example - Using % wildcard (percent sign wildcard)
The first MySQL LIKE example that we will look at involves using the % wildcard (percent sign wildcard).
Let's explain how the % wildcard works in the MySQL LIKE condition. We want to find all of the customers whose last_name begins with 'Sm'.
SELECT customer_name
FROM customers
WHERE last_name LIKE 'Sm%';
You can also using the % wildcard multiple times within the same string. For example,
SELECT customer_name
FROM customers
WHERE last_name LIKE '%it%';
In this MySQL LIKE condition example, we are looking for all customers whose last_name contains the characters 'it'.
Example - Using _ wildcard (underscore wildcard)
Next, let's explain how the _ wildcard (underscore wildcard) works in the MySQL LIKE condition. Remember that _ wildcard is looking for only one character.
For example:
SELECT supplier_name
FROM suppliers
WHERE supplier_name LIKE 'Sm_th';
This MySQL LIKE condition example would return all suppliers whose supplier_name is 5 characters long, where the first two characters are 'Sm' and the last two characters are 'th'. For example, it could return suppliers whose supplier_name is 'Smith', 'Smyth', 'Smath', 'Smeth', etc.
Here is another example:
SELECT *
FROM suppliers
WHERE account_number LIKE '12345_';
You might find that you are looking for an account number, but you only have 5 of the 6 digits. The example above, would retrieve potentially 10 records back (where the missing value could equal anything from 0 to 9). For example, it could return suppliers whose account numbers are:
123450, 123451, 123452, 123453, 123454, 123455, 123456, 123457, 123458, 123459
Example - Using NOT Operator
Next, let's look at how you would use the NOT Operator with wildcards.
Let's use the % wilcard with the NOT Operator. You could also use the MySQL LIKE condition to find suppliers whose name does not start with 'G'.
For example:
SELECT supplier_name
FROM suppliers
WHERE supplier_name NOT LIKE 'G%';
By placing the NOT Operator in front of the MySQL LIKE condition, you are able to retrieve all suppliers whose supplier_name does not start with 'G'.
Example - Using Escape Characters
It is important to understand how to "Escape Characters" when pattern matching. These examples deal specifically with escaping characters in MySQL.
Let's say you wanted to search for a % or a _ character in the MySQL LIKE condition. You can do this using an Escape character.
Please note that you can only define an escape character as a single character (length of 1).
For example:
SELECT *
FROM suppliers
WHERE supplier_name LIKE 'G\%';
Since we didn't specify an escape character, MySQL assumes that the "\" is the escape character. MySQL then assumes that the escape character is "\" which results in MySQL treating the % character as a literal instead of a wildcard. This statement would then return all suppliers whose supplier_name is G%.
We can override the default escape character in MySQL by providing the ESCAPE modifier as follows:
SELECT *
FROM suppliers
WHERE supplier_name LIKE 'G!%' ESCAPE '!';
This MySQL LIKE condition example identifies the ! character as an escape character. The ! escape character would result in MySQL treating the % character as a literal. As a result, this statement will also return all suppliers whose supplier_name is G%.
Here is another more complicated example using escape characters in the MySQL LIKE condition.
SELECT *
FROM suppliers
WHERE supplier_name LIKE 'H%\%';
This MySQL LIKE condition example returns all suppliers whose name starts with H and ends in %. For example, it would return a value such as 'Hello%'. Since we did not specify an escape character in the LIKE condition, MySQL assumes that the escape character is "\" which results in MySQL treating the second % character as a literal instead of a wildcard.
We could modify this LIKE condition by specfying an escape character as follows:
SELECT *
FROM suppliers
WHERE supplier_name LIKE 'H%!%' ESCAPE '!';
This MySQL LIKE condition example returns all suppliers whose name starts with H and ends in the literal %. For example, it would return a value such as 'Hello%'.
You can also use the escape character with the _ character in the MySQL LIKE condition.
For example:
SELECT *
FROM suppliers
WHERE supplier_name LIKE 'H%\_';
Again, since no ESCAPE modifier is provided, MySQL uses "\" as the escape character resulting in the _ character to be treated as a literal instead of a wildcard. This example would then return all suppliers whose supplier_name starts with H and ends in _. For example, it would return a value such as 'Hello_'.