Let us explore how to use the SQL like and not like operators using this tutorial.
SQL Like Operator
The SQL LIKE operator is used in conjunction with the WHERE clause to search for matching patterns.
The LIKE operator uses wildcard characters to specify pattern matching. There are two main wildcard characters supported:
- % – when used with the LIKE operator, the percentage sign denotes one or more characters zero.
- _ – and underscore signifies a single character.
We can express the syntax for the like operator as shown below:
FROM tbl_name
WHERE colN LIKE pattern;
SQL Like Examples
To illustrate how to use the LIKE and NOT LIKE operators in SQL. You can use the provided SQL statements below:
id serial primary key,
full_name varchar(255),
email varchar(255),
department varchar(100),
start_date date,
active bool,
category varchar(50)
);
insert into employees (full_name, email, department, start_date, active, category)
values
('Sam Davis', '[email protected]', 'Game Development', '2014-01-04', FALSE, 'DEV-G1'),
('Taylor Miller', '[email protected]', 'Game Development', '2019-10-06', TRUE, 'DEV-G1'),
('Wilson Smitty', '[email protected]', 'Database Developer', '2016-12-23', TRUE, 'DEV-DB1'),
('Barbara Moore', '[email protected]', 'Database Developer', '2015-12-23', TRUE, 'DEV-DB1'),
('Raj Chan', '[email protected]', 'Database Developer', '2017-12-23', FALSE, 'DEV-DB1'),
('Susan Brown', '[email protected]', 'DevOps Engineer', '2011-12-23', TRUE, 'DEV-OPS1'),
('Marial Anderson', '[email protected]', 'DevOps Engineer', '2015-12-23', TRUE, 'DEV-OPS1'),
('Nancy Thomas', '[email protected]', 'DevOps Engineer', '2014-12-23', FALSE, 'DEV-OPS1');
Once we have the sample data, we can proceed.
Example 1
To find any .com email in the sample data above, we can run a query as shown:
where email like '%.com%';
The ‘%.com%’ pattern tells SQL to match any characters before and after the .com pattern.
The query above should return:
Example 2
We can also use the LIKE operator to search for more precise patterns. For example, to search for the first name that contains the letter ‘E’ in the second position, we can run:
where full_name like '_e%';
In this case, the ‘_e% specifies one character.
The query should return:
Example 3
You can also combine multiple search patterns using the AND or OR operator. For example, to find the record where the first name contains the letter ‘E’ in the second position and has a .com email, we can do the following:
where full_name like '_e%' and email like '%.com%';
The above should combine both like patterns and return:
Example 4
To fetch the records containing the letter ‘E’ in the name or a .com email, we can run:
where full_name like '_e%' or email like '%.com%';
This should return either a .com email record or a record where the name contains the letter E in the second position.
Sample output is shown:
Example 5
Another example is searching for a pattern that starts with a specific letter. We can do:
where full_name like 'S%s';
The above query tells SQL to return any record where the name starts and ends with the letter s. The record can contain any number of characters between the first and last letter.
This should return:
SQL NOT LIKE Operator
There is not much difference between the syntax and usage of the LIKE and NOT LIKE operators. The only difference is that the NOT LIKE operator is used to negate the result of the LIKE operator.
For example, to exclude any .com emails, we can run:
where email not like '%.com%';
The above query should exclude all .com emails as shown in the output below:
That is all there is to the NOT LIKE operator. It is used to exclude a specific pattern from the result set.
Finishing
Using this article, you discovered how to use SQL’s LIKE and NOT LIKE operators to search and exclude specific patterns from a result set.
Thanks for reading!!
