[Basic Grammar] SQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. 

 

There are two different wildcards often used in conjunction with the LIKE operator:

• The percent sign % represents zero, one, or multiple characters 
• The underscore sign _ represents one, single charater 

 

• Syntax 

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

 

• The_Wildcard 

The _ wildcard represents a single character and it can be any character or number.

 

Example 

Return all customers from a country that starts with 'S' followed by one wildcard character, then 'in' at the end: 

SELECT * FROM Customers
WHERE country LIKE 'S__in';

 

• Contains

To return records that contains a specific letter or phrase, add the % both before and after the letter or phrase. '

 

Example 

Return all customers that containts the phrase 'as'

SELECT * FROM Customers
WHERE CustomerNames LIKE '%as%';

 

• Combine Wildcards 

% and _ can be used in combination with other wildcards.

 

Example 

Return all customers that starts with "h" and are at least 5 characters in length:

SELECT * FROM Customers
WHERE CustomerName LIKE 'h____%';

Example

Return all customers that have "a" in the second position:

SELECT * FROM Customers 
WHERE CustomerName LIKE '-a%';

 

• Using the [] Wildcards 

The [] wildcard gives you a result if any of the cahractesr inside gets a match. 

 

Example 

Return all the gifts starting with either "b", "c", or "d".

SELECT * FROM Gifts 
WHERE GiftName LIKE '[bcd]%';

 

Exercises 

1. Write an SQL query to select all customers whose "City" starts with the letter "M."

SELECT * FROM Customers 
WHERE city LIKE 'M%';

 

2. Write an SQL query to return all brands from the "Brands" table whose names start with "C" and have at least 4 characters in their name.

SELECT * FROM Brand
WHERE BrandName LIKE 'C___%';