MySQL LIKE Operator

This SQL tutorial focuses on the MySQL LIKE operator, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts explaining how to use the WHERE clause in MySQL. To read additional posts regarding this subject, please use the following links:

• Introduction – MySQL WHERE clause
• Basic comparison operators
• IN operator
• BETWEEN operator
• LIKE operator
• IS NULL operator
• AND & OR operators

MySQL LIKE Operator

In MySQL, You may not always know the exact value to search for, sometimes you may want to select rows that match a certain character pattern, for example:

• All customers whose first name starts with ‘M’ (First Name column).
• All employees whose mobile phone starts with 054 (Cell Phone column).
• All vehicles whose license plate ends in 86 (License Plate Number column).

The MySQL LIKE operator is used to perform a wildcard searches and retrieve rows that match a certain character pattern.

SELECT ..
FROM ..
WHERE  column_name  LIKE 'pattern'

MySQL LIKE – Wildcards Operators

Two symbols can be used to construct the search string:

% – The percent (%) sign, represents any sequence of characters (0 or more).

_ – The underscore (_) sign, represents any single character.

The following MySQL examples demonstrate the usage of these symbols:

SELECT bookName
FROM books
WHERE bookName LIKE '%e'

• ‘%e’ – all of the book names that end in the letter ‘e’. This MySQL LIKE example actually puts no lower or upper limit on the number of letters before the last letter – ‘e’ (any number of characters is allowed), but requires the string to end in the letter ‘e’.
• Values that meet this condition are, for example: ‘Dune’, ‘Deliverance’, ‘Of Human Bondage’.
• Theoretically, a book whose name is : ‘e’ (just the letter ‘e’) would have also been included here, because % represents any number of characters, including zero.

SELECT bookName
FROM books
WHERE  bookName LIKE 'S%'

• ‘S%’ – all of the book names that begin in the letter ‘S’. This MySQL LIKE example actually puts no lower or upper limit on the number of letters after the first letter (any number of characters is allowed), but requires the word to begin with the letter ‘S’.
• Values that meet this condition are, for example: ‘Shane’, ‘Scoop’, ‘Sophie’s Choice’.
• String values in MySQL are not case sensitive; therefore, a book whose name is ‘stranger in a strange land’ (starts with a lowercase ‘s’) would have also been included here.
• Theoretically, a book whose name is  ‘S’ (just the letter ‘S’) would have also been included here, because % represents any number of characters, including 0.

SELECT bookName
FROM books
WHERE  bookName LIKE '%a%'

• ‘%a%’ – all of the book names that contain the letter ‘a’. This MySQL LIKE example actually puts no lower or upper limit on the number of letters before or after the letter ‘a’ (any number of characters is allowed), but requires the word to contain the letter ‘a’.
• Values that meet this condition are, for example: ‘The Great Gatsby’, ‘Catch-22’, ‘Fear’, ‘Animal Farm’, ‘Lolita’.
• Theoretically, a book whose name is  ‘a’ would have also been included here, because % represents any number of characters, including 0.

SELECT bookName
FROM books
WHERE  bookName LIKE '_a%'

• ‘_a%’ – all of the book names whose second letter is ‘a’.
• This SQL Select statement allows any single character to appear as the first character of the name ( _ stands for a single character). The second character of the name must be equal to ‘a’. After the letter ‘a’, there is no lower or upper limit to the letters that can follow.
• Values that meet this condition are, for example: ‘Battlefield Earth’, ‘Darkness at Noon’ or ‘Pale Fire’.

MySQL NOT Operator

This MySQL operator produce opposite results than those produced by the LIKE operator.

The NOT keyword is written before the LIKE keyword.

This MySQL LIKE example would retrieve all books whose name contains the letter ‘n’ :

SELECT bookName
FROM books
WHERE  bookName LIKE '%n%'

This MySQL NOT LIKE example would retrieve all books whose name does not contain the letter ‘n’ :

SELECT bookName
FROM books
WHERE  bookName NOT LIKE '%n%'