Databases are a tool used to store information on computers. Information in a database can be a variety of different data types—such as numbers, strings, and dates—and the data can be queried at any time. For example, a database could be used to store a list of employees who work for an organization. Then, you could write queries to get the birthdays of every employee or get the total size of the workforce.
Often, the information you query is specific. Perhaps you want to get every employee whose name is
Paul, or every employee who is an executive.
However, there are many times where you may want to get information that matches a certain pattern. Perhaps you want to get every employee whose name starts with
H, or the names of everyone who was born in 1997.
That’s where the
SQL LIKE operator comes into play. In this article, we’ll break down how to use the
LIKE operator on an
SQL server to perform more specific queries.
Database Query Refresher
Before we start to look at the
SQL LIKE operator, we should remind ourselves of the basics of database queries.
Databases consist of tables that store data. You can run a query on these tables to get records that meet your conditions. For example, you can run a query to get every employee whose name is
Alex or every employee who works at the San Francisco branch of your company.
Here’s the basic structure of an
SELECT column_name FROM table_name WHERE your_conditions;
For example, if we wanted to get a list of employee names who work at the San Francisco branch of our company, we could run the following query:
SELECT name FROM employees WHERE branch = 'San Francisco';
Our database may return a response like this:
Right now, our query only gets employees who work at the San Francisco branch. But what if we wanted to get every employee whose name starts with
G? We would have to use the
SQL LIKE operator to perform this function.
SQL LIKE operator can be used to search for a specific pattern within a record. For example, we can use
LIKE to get all employees whose name ends in
S, or whose title includes
LIKE operator makes use of wildcard characters to get data that matches a certain pattern. The two wildcards often used with the
LIKE operator are as follows:
_ – The underscore character represents a single character.
% – The percent sign represents zero, one, or more characters.
Here is the structure of a query using the
SELECT column_name FROM table_name WHERE column_name LIKE pattern;
Let’s use an example to illustrate how
SQL LIKE works in more depth. In the below example, we retrieve the names and branches of every employee who works at a branch whose name starts with the letter
SELECT name, branch FROM employees WHERE branch LIKE 'S%';
Here is the output from our query:
The percent sign after the letter
S tells our code that we want to get every branch whose name starts with the letter
If we wanted to get the names and titles of every employee who is an associate, including senior associates, we could use this query:
SELECT name, title FROM employees WHERE title LIKE '%Associate%';
Our query runs a pattern matching search to find all employees whose names meet the
‘%Associate%’ rule, and returns the following:
|Geoff||Senior Sales Associate|
In addition, we can use the
NOT LIKE operator to select all records that do not meet a certain condition. For example, we could use
NOT LIKE to get the names of every employee whose name is not four characters long. Here is an example:
SELECT name FROM employees WHERE name NOT LIKE '____';
Our query will skip over every employee whose name is not four characters and return the rest. Each underscore represents one character, and because we have four underscores in our
NOT LIKE statement, our query knows to exclude any names that meet the condition.
Here is what our database returned:
That’s all there is to it—the
LIKE operator is simple when you know how to use it.
In this article, we discussed how queries work in
SQL and how you can use the
LIKE SQL statement to make your queries more specific. We have also discussed how you can use the
NOT LIKE statement to get information that does not meet specific rules.