SQL Like: A How-To Guide

The SQL LIKE operator uses wildcards to find data similar to a particular pattern. You can use an underscore to substitute for a single character or a percentage sign to substitute zero, one, or multiple characters.

Have you ever wanted to retrieve information from columns which contain a value similar to another? For instance, say you have a table called employees. Maybe you want to retrieve a list of employees whose title contains “Sales”.

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.

SQL LIKE

The SQL LIKE operator finds records in a database whose column values match a particular pattern. Patterns are expressed using percent signs, underscores, and the text for which you are searching.

For example, we can use LIKE to get all employees whose name ends in S, or whose title includes Associate.

The 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 LIKE operator:

SELECT column_name FROM table_name
WHERE column_name LIKE pattern;

In this syntax, we use the LIKE operator in an SQL SELECT statement. You can use this operator in INSERT, UPDATE, and DELETE statements.

Let’s use an example to illustrate how SQL LIKE works.

SQL LIKE Example

Say we have a table with information on the employees who work for a business. We want to retrieve the names and branches of every employee who works at a branch whose name starts with the letter S.

To do this, we can use the following query:

SELECT name, branch FROM employees
WHERE branch LIKE 'S%';

The SQL server database returns:

(3 rows)

The percent sign after the letter S tells our code that we want to get every branch whose name starts with the letter S. Our query returns every character string match.

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 finds all employees whose names match the pattern ‘%Associate%’. The query returns the following:

SQL NOT LIKE Example

We can use the NOT LIKE operator to select all records that do not meet a certain condition. The NOT LIKE statement is a combination of the SQL NOT and the LIKE statement. The NOT statement finds records which do not meet a 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 SQL query we could use for this purpose:

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 wildcard underscore represents one character. If a name is more or less than four characters long, it will not show up in our results set.

Our database returns all records that match our search pattern:

(4 rows)

Conclusion

The SQL LIKE operator lets you use patterns to find records in a database. You can use this record to find similar records without specifying an exact match which must be met. The LIKE operator appears in the WHERE section of an SQL statement.

In this article, we discussed 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.

We have written an ultimate guide for SQL learners to help you acquire the knowledge you need to become a professional database developer. Check it out on our How to Learn SQL page.

"Career Karma entered my life when I needed it most and quickly helped me match with a bootcamp. Two months after graduating, I found my dream job that aligned with my values and goals in life!"

Venus, Software Engineer at Rockbot

Find Your Bootcamp Match