SQL LIKE Operator Examples

What is SQL LIKE Operator

The SQL LIKE operator is used for searching specific patterns in the column. It is always used with the WHERE clause and we can combine any number of conditions using AND or OR operators. 

There are two wildcards often used mutually with the LIKE operator:

1. The “%” sign represents zero, one, or multiple characters. For example: “a%”, it will search for the name that starts with “a”. There is no size limitation. 
2. The “_” underscore represents a single character. You can add multiple underscores to define fixed length. For example: “a___”, it will find the name that starts with “a” and has a fixed length of 4.

Before we dive into practical examples of LIKE, let’s have a look at various examples of wildcards and descriptions. 

SQL LIKE Examples

In this section, we will learn about 9 different ways of using a LIKE operator on a sample dataset. The SF Salaries dataset consists of San Francisco city government employees and how they are compensated. It contains annual compensation from 2011 to 2014. 

In our case, we will be only focusing on EmployeeName, JobTitle, BasePay, OvertimePay, OtherPay, and TotalPay, as shown below. 

Note: I am using online SQL query tools (sqliteonline.com) to create examples.  

Example #1

It will display all of the columns where EmployeName is “Nathaniel Ford”. You can also use the “=” equal sign instead of LIKE, but wildcards do not work with equal signs.

SELECT *

FROM Salaries

WHERE EmployeeName LIKE 'Nathaniel Ford';

Example #2

The query will display the top 5 employee names that start with “Nathaniel”. As we can observe, we got Nathaniel Ford, Nathaniel Steger, Nathaniel Yuen, etc. 

The “%” at the end represents any character after that.

SELECT *

FROM Salaries

WHERE EmployeeName LIKE 'Nathaniel%'

LIMIT 5;

Example # 3

The query will search for all of the employee names that end with “Ford” and display the top five results. 

SELECT *

FROM Salaries

WHERE EmployeeName LIKE '% Ford'

LIMIT 5;

Example #4

The query will search for all of the employee names that have “Ford” in any position and display top five results. 

We got Ray Crawford, Kieth Sanford, John Sanford Jr, etc.

SELECT *

FROM Salaries

WHERE EmployeeName LIKE '%Ford%'

LIMIT 5;

Example #5

The query will search for all of the employee names that start with “k” and end with “o”. For example, we got Kenneth Esposto. 

SELECT *

FROM Salaries

WHERE EmployeeName LIKE 'k%o'

LIMIT 5;

Example #6

We will be using underscore to search for employee names that have “t” at second and “h” at third position. 

We got Ethan Jackson, Otha Cotton, Ethelbert Ogbuehi, etc. They all have “that second position. 

SELECT *

FROM Salaries

WHERE EmployeeName LIKE '_th%'

LIMIT 5;

Example #7

This is an interesting query, where we are finding the employee name that has “kenn” at first 4 positions and two underscore followed by “h”. It means that the LIKE operator will find the first name that starts with “Kenn” and ends with “h”. The length of the first name is fixed to 7 characters.

SELECT *

FROM Salaries

WHERE EmployeeName LIKE 'Kenn__h%'

LIMIT 5;

Example #8

Let’s understand, underscore wildcards without “%”. We will find the name that ends with “ob” and has 9 characters. For that we have used 7 underscores to fix the length of the name to 9. 

As a result, we got only one name which is “Carly Gorb”.

SELECT *

FROM Salaries

WHERE EmployeeName LIKE '________ob'

LIMIT 5;

Example #9

We can use AND, OR, and NOT with LIKE operators. It can help you perform more specific searches. 

In the example, we will use NOT to display all the names except for the names that start with “ken”.

SELECT *

FROM Salaries

WHERE EmployeeName NOT LIKE 'ken%'

LIMIT 5;

Conclusion

The LIKE operators with wildcards can help you write effective queries and search for specific records. You can even use it with a logical function to narrow your search.

In this post, we have learned various ways of how we can use LIKE operators. I will highly recommend you to practice this on your own. You can even solve w3schools.com interactive exercises to understand the concept better. 

“Do let me know in the comments below, if you want to see this type of content more often.” 

 
 
Abid Ali Awan (@1abidaliawan) is a certified data scientist professional who loves building machine learning models. Currently, he is focusing on content creation and writing technical blogs on machine learning and data science technologies. Abid holds a Master's degree in Technology Management and a bachelor's degree in Telecommunication Engineering. His vision is to build an AI product using a graph neural network for students struggling with mental illness.