MySQL LIKE Operator – Partial Matching Conditional Queries

In this tutorial, we will learn about the MySQL LIKE Operator. Suppose you are going through a record of names and you want to find out names beginning with ‘P’. Furthermore, what if you want to find names that end with ‘r’. For these purposes, MySQL provides us with the LIKE Operator.

The MySQL LIKE operator is used with the WHERE clause to check whether a string contains the specified pattern or not. MySQL provides us with two wildcard operators for the same.

Wildcard Operators For MySQL LIKE operator

Wildcard operators are used to substituting one or more characters in a string to construct a pattern. For the LIKE operator, we use the following two wildcard operators,

• % – The percentage sign is used to match any string with zero, one or more characters.
• _ – The underscore is used to match any single character.

So if you want to specify a pattern for a name starting with ‘P’, then the pattern is ‘P%’. This means the string should start with P followed by zero or any number of characters.

What if you want to find names in which the second character is ‘r’? The pattern for that is ‘_r%’. The underscore at the beginning of the pattern means that the string should start with any single character. It should then be followed by ‘r’ in the second position followed by zero or any number of characters.

Syntax of MySQL LIKE Operator

SELECT expressions FROM table_name WHERE column_name LIKE pattern;Code language: SQL (Structured Query Language) (sql)

Examples of MySQL LIKE Operator

Consider the following ConferenceGuests table.

Let us now look at some examples to see how the LIKE operator works.

1. Finding Values That Start With A Character

Let us find out which names start with the letter ‘J’. We do so using the SELECT query,

SELECT * FROM ConferenceGuests WHERE Name LIKE ‘J%’;Code language: SQL (Structured Query Language) (sql)

We get the output as follows,

2. Finding Values That Have A Particular Value At A Particular Position

How about finding all records in which the State name has the character ‘a’ in the second position? We do this using the following query,

SELECT * FROM ConferenceGuests WHERE State LIKE ‘_a%’;Code language: SQL (Structured Query Language) (sql)

We get the output as follows,

3. Finding Values That End With A Particular Character

Let us find out all records in which the country name ends with ‘n’. We do so using the query:

SELECT * FROM ConferenceGuests WHERE Country LIKE ‘%n’;Code language: SQL (Structured Query Language) (sql)

The % sign implies that the string can have zero or more character before ‘n’ which is the last character in the string. We get the output as follows:

4. Finding Values With A Particular Length And With Beginning And End Character

How about finding a Country name that starts with ‘I’, has 3 characters in between and ends with ‘a’. We do this using the query,

SELECT * FROM ConferenceGuests WHERE Country LIKE ‘I___a’;Code language: SQL (Structured Query Language) (sql)

Note that you need to have three underscore signs between ‘I’ and ‘a’.

The output is as follows,

5. Finding a Value Beginning and Ending With Particular Characters

Let us find all records in which the guest name starts with ‘J’ and ends with ‘a’. The query used for this is,

SELECT * FROM ConferenceGuests WHERE Name LIKE ‘J%a’;Code language: SQL (Structured Query Language) (sql)

The output is as follows,

6. Finding Values Having Certain Characters In Them

Let us find out all records in which the State name has ‘ta’ in them. This means ‘ta’ can be anywhere in the string. It could start with, or end with ‘ta’ or can have it anywhere in the string. We use the following query for the same,

SELECT * FROM ConferenceGuests WHERE Name LIKE ‘%ta%’;Code language: SQL (Structured Query Language) (sql)

The output is as follows,

7. Using the MySQL LIKE operator with Logical Operators

You may combine the LIKE operator with the NOT operator to get the NOT LIKE operator. This will show you all records which do not match the pattern. Furthermore, you may also use multiple conditions along with the LIKE operator using the AND and OR operators. 

Let us find those records in which the guest name does not begin with an ‘A’ and the country name ends with an ‘a’. We use the following query,

SELECT * FROM ConferenceGuests WHERE Name NOT LIKE 'A%' AND Country LIKE '%a';Code language: SQL (Structured Query Language) (sql)

So, as you can see, we are finding Names that do not start with an ‘A’ and Country names that do end with an ‘a’.

The output we get is,

Conclusion

The MySQL LIKE operator lets you find any pattern within a column. There are endless possibilities that you can have with the patterns using the LIKE operator. For further reading, please refer to the links below.

References

• JournalDev article on MySQL LIKE operator.
• MySQL documentation on the LIKE operator.