Description

The SQL keyword LIKE is an operator 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.

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.

SELECT With LIKE

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

Run this on IDE

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

UPDATE With LIKE

The below SQL 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%';

DELETE With LIKE

The below SQL 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%';

Related Links