MySQL LIKE – Tutorial With Syntax And Usage Examples

This tutorial explains pattern matching using the MySQL LIKE operator:

Pattern matching is an important feature in almost all programming languages like – Java/C#/Python etc which mostly leverage the usage of Regular Expressions for pattern matching against a given string input.

MySQL provides an easy to use operator called LIKE, which could be used to match the String values against specified patterns and are useful for querying large sets of data.

=> Click here for the complete MySQL tutorial series

MySQL LIKE

Syntax:

The LIKE operator is used along with the WHERE clause in a SELECT query.

In a simple query, the syntax looks like below.

SELECT {column_names} [*] FROM {table_name} WHERE {column_with_string_value} LIKE {match_pattern}

Different components of this syntax are explained as follows:

• {column_names}: These are the column names to be displayed as an output of the SELECT query. These values can be * for selecting all the columns or comma-separated names of individual columns.
• {table_name}: This is the name of the table where the query needs to be executed.
• {column_with_string_value}: This is the column whose values need to be executed against the specified pattern. Please note that any comparison is done on a column that can be converted to String.
• {match_pattern}: This is the match expression against which column values need to be compared. A sample of match patterns can be – ‘SM%’. This would match all column values starting with SM. Example: SMART, SMELL, SMOKE, etc.

Similar to this query, the LIKE operator can be used even with complex queries with JOINS, etc as the LIKE is just a comparison operator and can be used wherever column value comparison is possible.

Note: Similar to LIKE, we can also use its negated variant, which is ‘NOT LIKE’. So in this case, instead of returning the matching results, the queries with the ‘NOT LIKE’ operator will return results that are non-matching.

MySQL Match Patterns

LIKE Operator can be used along with 2 types of patterns. These are enlisted in the below table:

Now we will see some examples using both the patterns.

% Match Pattern

% pattern is used when you want to match 0 or more characters after or before its placement.

For example: if you want to match a string ‘it’ from a column having employee names. Suppose there are names like – Amit, Anchit, Arpit, Nikita, Smith, etc. We can see all the names have the ‘it’ substring. But their positioning is different.

Suppose the column name is names and the table name is student_names.

Use the following sample data creation scripts to populate the database:

CREATE TABLE student_names (name VARCHAR(100));

INSERT INTO student_names VALUES('Amit'),('Ankit'),('Smith'),('Nikita'),('Mohit');

Let’s see how we can use the % Match pattern.

SELECT * FROM student_names WHERE name LIKE '%it%'

Output:

Now what this means is that it can match a string that has the ‘it’ substring at any location and there can be any number of characters before and after the match. You can see that all the matching names are returned as output.

Rewrite the query to have just those names matched which are ending with the ‘it’ substring.

SELECT * FROM student_names WHERE name LIKE '%it'

So here, we have removed the trailing ‘%’ sign and have placed the ‘it’ string at the end. This pattern would allow any number of characters before ‘it’ but the String should end with the ‘it’ substring.

Output of the above query:

Note: It’s important to note here that the pattern mentioned using the ‘%’ character is case insensitive. So for the above example, instead of ‘%it’ we could have used ‘%IT’ or ‘%It’, the output would have still remained the same.

_Match Pattern

Let’s now have a look at how we can use the ‘_’ Match pattern. We know that ‘_’ allows exactly one character.

Suppose we want to query all the 5 letter names from the same table (student_names).

To use ‘_’ pattern matcher in this case, we have specified five _ (Underscores) along with LIKE Pattern which will match only those names which are 5 characters in length.

SELECT * FROM student_names WHERE name LIKE '_____'

Output:

Let’s look at another example. Suppose we want to find all 5 letter names that are ending with the substring ‘it’

SELECT * FROM student_names WHERE name LIKE '___it'

Here we have used 3 ‘_’ (underscores) and the actual substring that need to be matched.

Output:

Combining % And _ Pattern Matcher

In this section, we will discuss how we can match the % and _ pattern matchers together.

Suppose we want to find all the names that have ‘it’ substring and can have at exactly 2 characters after the word ‘it’

SELECT * FROM student_names WHERE name LIKE '%it_'

Here we have combined both % and _ match patterns to find the exact match.

Output:

Using NOT LIKE

NOT LIKE is the exact negation of what the LIKE operator returns. i.e. it will return all the records/rows that do not match the pattern-match expression.

Example: Suppose we want to find all the names that are not 4 characters long.

SELECT * FROM student_names WHERE name NOT LIKE '____'

Output:

You can see in the above output, it returns only those names that are not 4 characters long.

Using MySQL LIKE With ESCAPE Character

Here we will see how we can use ESCAPE characters along with the pattern matcher.

Suppose in a hypothetical situation we have names that actually contain % and _ characters and we want to find those names, then we need to actually match % & _. This can be achieved using an escape character.

Note: Default value of escape character is ‘\’ (Backslash)

Add some data to the student_names tables that contain names with % and _ characters.

INSERT INTO student_names 
VALUES('Darre%n'),('Julia_Roberts'),('Dane_Sherman%');

Below are some examples to understand this better.

#1) Find all the names that have a % sign.

SELECT * FROM student_names WHERE name LIKE '%\%%'

Output:

#2) Find all names that have an _ sign

SELECT * FROM student_names WHERE name LIKE '%\_%'

Output:

In both the above examples, you can see that we have used an escape character to mention the % and _ characters – i.e. for matching a % character itself we have used an escape character before the % sign – i.e. like ‘\%’

Using An Escape Character Of Your Own Choice

Now let’s try to extend the previous example.

MySQL allows you to specify your own ESCAPE character that MySQL should use while running the pattern matching search.

Let’s try updating the Escape character to ‘!’ instead of the default value ‘\’

SELECT * FROM student_names WHERE name LIKE '%!_%' ESCAPE '!'

Output:

In the above example, we have overridden the escape character to ‘!’ and used the same in the pattern matcher ‘%!_%’ – where we want to match an ‘_’ character in the name.

Also read =>> Escape Sequences in Java

Frequently Asked Questions

Q #1) What does %% mean in SQL?

Answer: ‘%%’ is not going to match anything in specific. Its equivalent to just having 1 % character. If you want to match the ‘%’ character itself in the column value you can use it with an escape character which has a default value of \’.

Suppose you want to match a column value having a ‘%’ character, you can write pattern match as – LIKE ‘%\%%’ (notice an extra backslash before the middle percentage (%) character.

Q #2) What is a wildcard in MySQL?

Answer: MySQL LIKE Operator works with 2 wildcard characters in MySQL to achieve different ways of pattern matching.

These are:

• % – This would match any number of characters (including zero)
• _ – This would match exactly one character

Q #3) How do I write a LIKE query in MySQL?

Answer: LIKE is a simple operator that is generally used along with the WHERE clause in a SELECT query. It’s used to match the column values against the specified pattern.

Example: Suppose there’s a table having employee_details, and it has a column named address which contains house no, street name, etc. You want to find out all employees that have street defined as ‘Oxford Lane’ as their address.

We can use the LIKE operator to write such a query.

SELECT * FROM employee_details WHERE address LIKE '%Oxford Lane%'

Please note that the String patterns are case insensitive while being matched against the specified match expression.

Q #4) How to specify a different Escape character along with the LIKE Operator?

Answer: MySQL provides a way to specify a custom Escape character which would override the default one i.e. \

Here is an example which would change the escape character to ‘!’ for the query execution.

SELECT * FROM employee_details WHERE address LIKE '%!_%' ESCAPE '!'

In the above query, we have mentioned ‘!’ as the escape character and used the same in the match expression.

Q #5) How to match an actual % or _ character using the LIKE Operator?

Answer: The characters like % and _ are special wildcard characters which when actually need to be matched as part of the String should be escaped.

The default escape character is ‘\’ and should be used just before mentioning the wildcard character.

Suppose we want to match the ‘_’ character against a given column value, then we can write the match expression as – LIKE ‘%\_%’

Conclusion

In this tutorial, we learned about the different ways in which we can use the LIKE operator (or the negated version, i.e. NOT LIKE).

We also discussed the supported wildcard characters i.e. _ and % and how can they be escaped if they need to be part of the String to be searched/matched.

The LIKE operator is a powerful functionality provided by MySQL and is generally used to filter records while querying large sets of data.