The SQL Like is a logical operator that is used to determine whether a specific character string matches a specified pattern. It is commonly used in a Where clause to search for a specified pattern in a column.
This operator can be useful in cases when we need to perform pattern matching instead of equal or not equal. The SQL Like is used when we want to return the row if specific character string matches a specified pattern. The pattern can be a combination of regular characters and wildcard characters.
To return a row back, regular characters must exactly match the characters specified in the character string. The wildcard characters can be matched with arbitrary parts of the character string.
Let’s use the AdventureWorks sample database and see some different SQL Like operators with ‘%’ and ‘_’ wildcards.
Using the % wildcard character (represents zero, one, or multiple characters)
The query below returns all telephone numbers that have area code “415” in the “PersonPhone” table:
|
1 2 3 4 5 6 7 |
SELECT p.FirstName, p.LastName, ph.PhoneNumber FROM Person.PersonPhone AS ph INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityID WHERE ph.PhoneNumber LIKE '415%' ORDER by p.LastName; GO |
Notice that ‘415%’ symbol is specified in the Where clause. What this means is that SQL Server will search for the number 415 followed by any string of zero or more characters. Here is the result set:
The Not logical operator reverses the value of any Boolean expression. So, if we just specify Not like with the ‘%’ wildcard character in SQL Like clause, add one additional condition and place it in the same statement as above we should get a query like this:
|
1 2 3 4 5 6 7 |
SELECT p.FirstName, p.LastName, ph.PhoneNumber FROM Person.PersonPhone AS ph INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityID WHERE ph.PhoneNumber NOT LIKE '415%' AND p.FirstName = 'Gail' ORDER BY p.LastName; GO |
This time, the query returned all the records in the “PersonPhone” table that have area codes other than 415:
Furthermore, let’s say that we want to find all the records where a name contains “ssa” in them. We can use the query below:
|
1 2 3 4 5 |
SELECT p.FirstName, p.LastName FROM Person.Person AS p WHERE LastName LIKE '%ssa%' ORDER BY p.LastName; GO |
Notice that by using ‘%’ before and after “ssa”, we are telling the SQL Server to find all records in which “Person.Person” has “ssa” characters and it doesn’t matter what other characters are before and after “ssa”:
Using the “_” wildcard character (represents a single character)
The SQL Like underscore character e.g. „ is used when we want to check a single character that can be anything and provide the rest of the characters for our match.
Let’s say that if we want to return all records wherein the “FirstName” table first character can be anything but rest of them should be “en”. Use the query below:
|
1 2 3 4 5 |
SELECT p.FirstName, p.MiddleName FROM Person.Person p WHERE p.FirstName LIKE '_en'; GO |
Here is the result set:
Note that a combination of wildcards character can also be used at the end of the search pattern. For example, to return all telephone numbers that have an area code starting with 6 and ending in 2 in the “PersonPhone” table use the following query:
|
1 2 3 4 |
SELECT pp.PhoneNumber FROM Person.PersonPhone pp WHERE pp.PhoneNumber LIKE '6_2%' GO |
Note that the ‘%’ wildcard character is used after the underscore character since the area code is the first part of the phone number and additional characters exist after in the column value:
Using the [ ] square brackets (any single character within the specified range [a-t] or set [abc])
The SQL Like operator with square brackets is used when we want to have range. Let’s say if we want to find all the rows where “FirstName” first character start with [a-f]. Use the query below:
|
1 2 3 4 |
SELECT BusinessEntityID, FirstName, LastName FROM Person.Person WHERE FirstName LIKE '[a-f]%'; GO |
As it can be seen we have used range [a-f]%. That means to return the first character from a to f and after that, any characters are fine because we used ‘%’ afterward:
To return any single character within a set use the example below to find employees on the “Person” table with the first name of Cheryl or Sheryl:
|
1 2 3 4 |
SELECT BusinessEntityID, FirstName, LastName FROM Person.Person WHERE FirstName LIKE '[CS]heryl'; GO |
This query will return only Cheryl in this case, but it would have returned Sheryl as well if we had any records in the database:
Here’s another example when we actually have mixed results:
|
1 2 3 4 5 |
SELECT LastName, FirstName FROM Person.Person WHERE LastName LIKE 'Zh[ae]ng' ORDER BY LastName ASC, FirstName ASC; GO |
The above query finds the records for employees in the “Person” table with last names of Zheng or Zhang:
Using [^] square brackets (any single character not within the specified range [a-t] or set [abc])
As you might have guessed, this is the opposite of the previous usage of the SQL Like operator with square brackets. Let’s say that we want to return all the records where “FirstName” first character does not start with [a to f]:
|
1 2 3 4 |
SELECT BusinessEntityID, FirstName, LastName FROM Person.Person WHERE FirstName LIKE '[^a-f]%'; GO |
Notice that it only returned the records which do not start with any character from a to f:
With the set example, let’s say that we want to get all the records where “FirstName” does not start with a,d,j. We can use the query below:
|
1 2 3 4 |
SELECT BusinessEntityID, FirstName, LastName FROM Person.Person WHERE FirstName LIKE '[^adj]%'; GO |
Here is the result set:
Using the escape clause
This is one SQL Like predicate that is used to specify an escape character. The query below uses the Escape clause and the escape character:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
USE tempdb; GO IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'mytbl2') DROP TABLE mytbl2; GO USE tempdb; GO CREATE TABLE mytbl2(c1 SYSNAME); GO INSERT INTO mytbl2 VALUES('Discount is 10-15% off'), ('Discount is .10-.15 off'); GO SELECT c1 FROM mytbl2 WHERE c1 LIKE '%10-15@% off%' ESCAPE '@'; GO |
It returns the exact character string 10-15% in column c1 of the mytbl2 table:
I hope this article on the SQL Like operator has been informative and I thank you for reading.
He has written extensively on both the SQL Shack and the ApexSQL Solution Center, on topics ranging from client technologies like 4K resolution and theming, error handling to index strategies, and performance monitoring.
Bojan works at ApexSQL in Nis, Serbia as an integral part of the team focusing on designing, developing, and testing the next generation of database tools including MySQL and SQL Server, and both stand-alone tools and integrations into Visual Studio, SSMS, and VSCode.
See more about Bojan at LinkedIn
View all posts by Bojan Petrovic
- Visual Studio Code for MySQL and MariaDB development - August 13, 2020
- SQL UPDATE syntax explained - July 10, 2020
- CREATE VIEW SQL: Working with indexed views in SQL Server - March 24, 2020