The LIKE operator is used in SQL to compare a string to a pattern. It is often used with the wildcard characters “%” and “_“. The “%” character matches any sequence of zero or more characters and the “_“ character matches any single character. The LIKE operator is commonly used in conjunction with the SELECT statement to retrieve data that matches a certain pattern. In this article, we will explore the SQL LIKE operator with examples from the Northwind database in SQL Server.
The Northwind database is a sample database that is commonly used for SQL Server training and testing purposes. It contains tables for customers, orders, products, and other entities related to a fictional company that sells food products. We will be using the Customers and Orders tables in the Northwind database to illustrate the use of the LIKE operator.
Using the SQL LIKE Operator with the “%” Wildcard
Let’s start by using the “%” wildcard with the LIKE operator to retrieve customers whose company names contain the word “food”. Here’s the SQL query:
SELECT * FROM Customers
WHERE CompanyName LIKE '%food%';
This query will retrieve all customers whose CompanyName contains the word “food”. The “%” wildcard matches any sequence of zero or more characters, so this query will match “Foodies Inc.”, “Food for Thought”, “Good Food Market”, and any other customer whose CompanyName contains the word “food”.
Using the SQL LIKE Operator with the “_” Wildcard
Now let’s use the “_” wildcard with the LIKE operator to retrieve customers whose company names contain exactly four characters. Here’s the SQL query:
SELECT * FROM Customers
WHERE CompanyName LIKE '________';
This query will retrieve all customers whose CompanyName contains exactly 8 characters. The “_” wildcard matches any single character, so this query will match any customer whose CompanyName is exactly 8characters long.
Here is the result of that query n SQL Server:
Using the SQL LIKE Operator with Multiple Wildcards
We can also use multiple wildcards in a single LIKE operator. For example, let’s retrieve all orders whose ShipCity starts with the letter “B” and ends with the letter “o”. Here’s the SQL query:
SELECT * FROM Orders
WHERE ShipCity LIKE 'B%o';
This query will retrieve all orders whose ShipCity starts with “B” and ends with “o”. The “%” wildcard matches any sequence of zero or more characters, so this query will match “Boston”, “Bordeaux”, “Bolton”, and any other ShipCity that starts with “B” and ends with “o”.
Here are some more examples of how the SQL LIKE operator can be used to search for patterns in text data:
Retrieve all products whose product name starts with “Chai”
SELECT * FROM Products
WHERE ProductName LIKE 'Chai%';
This query will retrieve all products whose ProductName starts with the word “Chai”, such as “Chai”, “Chai Latte”, “Chai Tea”, and so on.
Combining LIKE with the OR Operator
Retrieve all customers whose contact title is either “Owner” or “Manager”
SELECT * FROM Customers
WHERE ContactTitle LIKE '%Owner%' OR ContactTitle LIKE '%Manager%';
This query will retrieve all customers whose ContactTitle contains either the word “Owner” or the word “Manager”. Note that we use the “%” wildcard to match any sequence of zero or more characters before or after the search term.
Retrieve all orders whose ship country is either “USA” or “Canada”
SELECT * FROM Orders
WHERE ShipCountry LIKE 'U%S%A' OR ShipCountry LIKE 'Canada';
This query will retrieve all orders whose ShipCountry is either “USA” or “Canada”. Note that we use the “%” wildcard in this case to get all the cases where country is fpr example “U.S.A”.
Combining LIKE with the AND Operator
Retrieve all customers whose company name contains the word “food” and whose city starts with the letter “S”:
SELECT * FROM Customers
WHERE CompanyName LIKE '%food%' AND City LIKE 'S%';
This query will retrieve all customers whose CompanyName contains the word “food” and whose City starts with the letter “S”, such as “Foodies Inc.” located in “Seattle”, “Good Food Market” located in “San Francisco”, and so on.
Retrieve all orders whose ship country is either “USA” or “Canada” and whose ship city contains the word “ville”:
SELECT * FROM Orders
WHERE ShipCountry LIKE 'USA' OR ShipCountry LIKE 'Canada'
AND ShipCity LIKE '%ville%';
This query will retrieve all orders whose ShipCountry is either “USA” or “Canada” and whose ShipCity contains the word “ville”, such as “Montreal” in Canada and “Louisville” in the USA.
Combining LIKE with Regular Expressions
Retrieve all products whose product name ends with “a”, “b”, “c”, or “d”
SELECT * FROM Products
WHERE ProductName LIKE '%[a-d]';
This query selects all rows from the “Products” table where the “ProductName” column ends with any character that is either “a”, “b”, “c”, or “d”. The “%” wildcard is used to match any sequence of zero or more characters before the character set, and the character set itself matches any one of the specified letters at the end of the string.
So, this query will return all products whose names end with any one of the letters “a”, “b”, “c”, or “d”. For example, it would match “Ikura”, “Tunnbroid”, “Rogede Soid”, “Pavlova”, and many other products that end with these letters. It will not match any products whose names do not end with these letters.
Note that the letter range specified in the character set is case-sensitive, so it will only match uppercase or lowercase versions of the specified letters. To match both uppercase and lowercase versions of the letters, you could use a character set like this: ‘%[a-dA-D]’.
Here is the result of this query in SQL Server.
Using LIKE with Regular Expressions and a Subquery
Retrieve all orders for which contact name ends with “a”, “b”, “c”, or “d”
SELECT * FROM Orders
WHERE CustomerID IN (
SELECT CustomerID FROM Customers
WHERE ContactName LIKE '%[aeiou]%'
);
This query selects all rows from the “Orders” table where the “CustomerID” column contains any value that matches a value in the “CustomerID” column of the “Customers” table, and where the “ContactName” column of the matching customer contains any string of characters that ends with any character that is either “a”, “b”, “c”, or “d”. The “%” wildcard is used to match any sequence of zero or more characters before the character set, and the character set itself matches any one of the specified letters at the end of the string.
So, this query will return all orders that were placed by customers whose ContactName ends with any one of the letters “a”, “b”, “c”, or “d”. The subquery first selects all customer IDs from the “Customers” table whose ContactName column ends with any one of these letters (returns 10 CustomerIDs), and then the main query selects all orders from the “Orders” table whose CustomerID matches one of the customer IDs returned by the subquery.
Note that the letter range specified in the character set is case-sensitive, so it will only match uppercase or lowercase versions of the specified letters. To match both uppercase and lowercase versions of the letters, you could use a character set like this: ‘%[a-dA-D]’.
Here is the result of this query in SQL Server:
Conclusion
The LIKE operator is a powerful tool for searching for patterns in text data in SQL. It provides a flexible and intuitive way to filter records based on specific text patterns by using various wildcard characters such as “%”, “_”, and “[]”. It allows for searching for specific text patterns that match a certain format, or for finding records that contain specific characters or character ranges.
The LIKE operator is widely used in SQL queries and is supported by many database management systems. However, it can be relatively slow when used on large datasets, and using regular expressions in combination with LIKE may be a more efficient way to search for complex patterns.
Overall, the LIKE operator is an essential tool for searching text data in SQL, and it is important for developers and database administrators to have a good understanding of its syntax and functionality in order to efficiently query and manipulate data in their databases.
Leave a Reply