An Overview of SQL LIKE Statement and Its Use Cases

SQL LIKE statement is used to search a similar phrase or pattern in a column. It supports ASCII and Unicode pattern matching. This statement is used in the WHERE clause to search the specified symbol. It also makes developers’ life easy if they are looking for an output related to some matching patterns from an enormous amount of the data stored in the column.

Using Wildcards with LIKE Statement

Wildcards are widely used with SQL LIKE statements to search our different types of requirements. This section will explain how to use LIKE statements with various wildcards. Let’s start with one of the very popular wildcards % and see how it will return results while using the LIKE statement.

Using the % Wildcard

SQL LIKE statement is widely used with a % wildcard character. If you use a % wildcard after or before any letter, number or word, SQL Server will search and list all patterns that start or end with that symbol. If you use any symbol between this wildcard, the result will display all strings or patterns which have that symbol at any place of that string or symbol.

Let’s look at this example. If you need to search all employees’ names starting with R or Ra, you just need to use ‘R%’ or ‘Ra%’ with the LIKE statement.

--Search all names starting with “R…”
SELECT * FROM Employee
WHERE name LIKE ‘R%’

--Search all names starting with “Ra…”
SELECT * FROM Employee
WHERE name LIKE ‘Ra%’

Here is the output of both queries.

Let’s see one more example. I will add % before characters R and Ra to the above query.

--Search all names that have the character “R…”
SELECT * FROM Employee
WHERE name LIKE ‘%R%’

--Search all names that have the character “Ra…”
SELECT * FROM Employee
WHERE name LIKE ‘%Ra%’

We can see the output below. The first query has returned 7 rows now whereas there are 4 rows in the above screenshot. The second query has restored the same result because there are only two names that have Ra characters together.

Similarly, you can get a different result if you will use % before these characters or any symbol like ‘%R’ and ‘%Ra’. It will search all names which are either ending with R or Ra.

Using the Underscore ”_ “ Wildcard

We can also use an underscore ‘_’ along with a % wildcard to simplify our searches. Let’s assume you have to search all names which have a second character as ‘a’ or let’ say the 4^th character as ‘a’. The underscore wildcard symbol will make our task easy here.

--Search all names which have a second character as ‘…a…’
SELECT * FROM Employee
WHERE name LIKE ‘_a%’

--Search all names which have the 4th character as ‘…a…’
SELECT * FROM Employee
WHERE name LIKE ‘____a%’

You can use even a combination of characters or words in the above example. You need to place your searchable symbol at the n^th place where n is the position you want to search that character or string or word. The underscore symbol will be used before that n^th position as shown in the above example. I have used an underscore at the first position and ‘a’ at the second position followed by a % wildcard in the first example.

Here is the output of both queries.

Using the [ ] Wildcard

This wildcard is very useful in case you are looking for a phrase or text which is almost similar. Let’s see an example.

Suppose you want to get the list of specific employees. It’s possible that several employees can have different spelling for their names like one or two letters different from the actual ones like Julie or Julee, Shyam or Shaam and Jignesh or Gignesh or Zignesh.

This wildcard [ ] is very helpful to get the list of such employees irrespective of their names having several spelling mistakes. Here is an example of how to use this wildcard to get the list of all similar employees or similar phrases.

We will use a wildcard [..] character at a place where there is a chance of spelling mistakes with all possible characters for that name. You will understand more by looking into the below examples.

SELECT name, city, age, gender, designation 
FROM Employee
WHERE name LIKE ‘Jul[ie]e%’
GO
SELECT name, city, age, gender, designation 
FROM Employee
WHERE name LIKE ‘[ZGJ]ignesh%’
GO
SELECT name, city, age, gender, designation 
FROM Employee
WHERE name LIKE ‘Sh[ya]am%’
GO

Here is the output which is returning all possible names of employees. Each character passed in wildcard character [ ] will search along with remaining spellings and return the output. Look at the second query where I have used ‘[ZGJ]ignesh’ means SQL Server will search each character given inside wildcard along with remaining spellings given outside bracket like Zignesh, Gignesh, and Jignesh.

SQL LIKE Statement

Although I hope you have understood how to use the LIKE statement to search various patterns in SQL Server with the help of the above examples. But let me show you some other use cases based on two tables I have in my database.

I will display the list of employees who have sold all automotive parts-related products along with the amount which is mentioned in the printable invoice template.

SELECT name, s.Product, s.Price 
FROM Employee AS e
JOIN Sales AS s
ON e.id=s.PersonID
WHERE s.Product LIKE ‘%Parts%’
GO

Here is the output of the above query.

Now, we can add the LIKE statement in the above example to filter out one step deeper. Let assume we want to fetch the above results for only those employees whose mobile phone number starts with 98 number. This example could be anything if you don’t want to filter mobile numbers, you can filter addresses with a similar symbol or PIN code with similar numbers, etc. Let me show the query result about all employees who have sold automotive parts and whose mobile phone number starts with 98 number.

SELECT name, s.Product, s.Price 
FROM Employee AS e
JOIN Sales AS s
ON e.id=s.PersonID
WHERE s.Product LIKE ‘%Parts%’
AND mobile LIKE ‘98%’
GO

The output of the above query is below. This is also a use case of using the LIKE statement with AND operator.

Let’s see another example of the LIKE statement where you want to get the list of products with the help of the first and last letters. I will consider the above example and modify it to display only those products which start with B and end with S. We can get it done by using a wildcard % between the first and the last letter. The below example will help you understand more about this.

SELECT name, s.Product
FROM Employee AS e
JOIN Sales AS s
ON e.id=s.PersonID
WHERE s.Product LIKE ‘B%S’
GO

Here is the output of the above query.

For further mastery of SQL, consider our article SQL AS: Usage, Examples, and How it Can Benefit You Best.

SQL NOT LIKE Statement

SQL NOT LIKE statement is used to search any symbol and return a result in which that character or symbol is not present. Let’s use some examples given in the above section to understand it more in sequence.

We have searched for all employees who have sold parts-related products in the above section. Now we will search for employees who have not sold any parts-related products. I will use the same T-SQL query as given above. The only change I will make is I will add NOT LIKE in place of the LIKE statement.

SELECT name, s.Product, s.Price 
FROM Employee AS e
JOIN Sales AS s
ON e.id=s.PersonID
WHERE s.Product NOT LIKE ‘%Parts%’
GO

Have a look at the below output of the above query.

Similarly, you can use other examples given in the above section to display their results.

Let me take one example. Suppose we need to search all employees whose name starts with any letter but not with R and their cities are not London.

SELECT name, Designation
FROM Employee 
WHERE name NOT LIKE ‘R%’
GO

SELECT name, Designation
FROM Employee 
WHERE name LIKE ‘R%’
AND city NOT LIKE ‘London’
GO

Have a look at the output of the above two SQL queries. The first query is displaying every name except the one which starts with the R letter. The second query has returned all names which start with the letter R and excluded London.

Conclusion

I have explained how to effectively use the SQL LIKE statement and NOT LIKE statement for strings, words, numbers, or any character-based search from the column data. We have also learned how to use various wildcards with LIKE statements to fetch the data in a more productive way.

Last modified: May 16, 2023