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.

Let's look at an example of the UPDATE statement using the LIKE operator to update table rows based on a string pattern.

Table Data

Before executing the query, 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

Example

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

Overall

We now know how to use a DELETE statement with a LIKE operator to delete table rows based on a string pattern.

Related Links