Description

The LIKE operator can be used in a WHERE clause to search for a specific pattern in a column, using the wildcard characters.

  • The wildcard character % (percentage sign) represents zero, one or more characters.
  • The wildcard character _ (underscore sign) represents a single character.
  • Both the wildcard character % and _ can be used in combinations to achieve the required pattern.
  • It can be used on any statement that allows a WHERE clause, like SELECT, UPDATE and DELETE statements.

The pattern matches are useful when we don't know the exact form of the search string.

If we want to search for multiple patterns, we can use the logical AND and OR operators to combine the conditions together.

Syntax

Here is the basic syntax of the LIKE operator.

  • The column_list represents the column list of a table
  • The table_name represent the table name.
  • The column_name is one of the columns on which the pattern is applied using the LIKE operator.
  • The pattern is the string pattern to search for on the column, created using the wildcard characters.
SELECT column_list 
FROM table_name
WHERE column_name LIKE pattern;

WHERE Conditions Using LIKE

Here is a list of some common WHERE conditions using the LIKE operator.

WHERE Condition Description
WHERE name LIKE 'a%' Finds all the values that start with "a".
WHERE name LIKE '%a' Finds all the values that end with "a".
WHERE name LIKE '%or%' Finds all the values that have "or" in any position, which can be a start, end, or in between.
WHERE name LIKE '_r%' Finds all the values that have "r" in the second position.
WHERE name LIKE 'a_%' Finds all the values that start with "a" and are at least 2 characters in length.
WHERE name LIKE 'a__%' Finds all the values that start with "a" and are at least 3 characters in length.
WHERE name LIKE 'a%o' Finds all the values that start with "a" and end with "o".
WHERE name LIKE 'Ar_' Finds all the values that start with "Ab" and are exactly 3 characters in length.
WHERE name LIKE '_un' Finds all the values that end with "un" and are exactly 3 characters in length.
WHERE name LIKE '_an_' Finds all the values that have 'an' as 2nd and 3rd characters, and are exactly 4 characters in length.
WHERE name LIKE '%ar_' Finds all the values that have 'ar' as 2nd and 3rd characters from the end, and are at least 3 characters in length.
WHERE name LIKE '_ar%' Finds all the values that have 'ar' as 2nd and 3rd characters from the start, and are at least 3 characters in length.

Table Data

Before executing any of the queries, consider the below table data.

customer_id customer_name address city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
2 Yoshi Nagase 9-8 Sekimai Musashino-shi Tokyo Japan 100
3 Dominique Perrier 25, rue Lauriston Paris France 75016
4 Martin Blank Via Monte Bianco 34 Turin Italy 10100
5 Thomas Hardy 89 Chiaroscuro Rd. Portland USA 97219

SELECT With LIKE

The below SQL statement returns all the customers having a name that starts with the letter 'M'.

Run this on IDE

SELECT * FROM customers
WHERE customer_name LIKE 'M%';

After successful execution, the output looks as below.

customer_id customer_name address city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
4 Martin Blank Via Monte Bianco 34 Turin Italy 10100

UPDATE With LIKE

The below SQL statement updates all the customers having a name that starts with the letter 'M'.

Run this on IDE

UPDATE customers
SET city = 'London'
WHERE customer_name LIKE 'M%';

After successful execution, the table contains the updated data as below.

customer_id customer_name address city country postal_code
1 Maria Anders Obere Str. 57 London Germany 12209
2 Yoshi Nagase 9-8 Sekimai Musashino-shi Tokyo Japan 100
3 Dominique Perrier 25, rue Lauriston Paris France 75016
4 Martin Blank Via Monte Bianco 34 London Italy 10100
5 Thomas Hardy 89 Chiaroscuro Rd. Portland USA 97219

DELETE With LIKE

The below SQL statement deletes all the customers having a name that starts with the letter 'M'.

Run this on IDE

DELETE FROM customers
WHERE customer_name LIKE 'M%';

After successful execution, the table doesn't contain the deleted records as shown below.

customer_id customer_name address city country postal_code
2 Yoshi Nagase 9-8 Sekimai Musashino-shi Tokyo Japan 100
3 Dominique Perrier 25, rue Lauriston Paris France 75016
5 Thomas Hardy 89 Chiaroscuro Rd. Portland USA 97219

Tips & Recommendation

MS Access uses the character * (asterisk sign) instead of % (percentage sign) and a ? (question mark sign) instead of _ (underscore sign) for patterns.

In MySQL, the non-binary string (like CHAR, VARCHAR, TEXT) comparisons are case-insensitive by default, whereas the binary strings (like BINARY, VARBINARY, BLOB) comparisons are case-sensitive. So, make sure we consider this while retrieving the results from a table.

  • For example, the WHERE clause WHERE name LIKE 'A% returns records that start with either A or a in MySQL.
  • To make such a comparison case-sensitive, use the BINARY comparison by using the WHERE clause WHERE BINARY name LIKE 'A%.

Overall

We now understood how to create SQL SELECT statements to fetch records using search patterns using the LIKE operator.

Related Links