SQL Standard

SQL NOT LIKE

The LIKE and NOT LIKE operators in SQL are used to search for specific patterns within a column. This allows you to filter for particular records that match a specified pattern.

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:

  1. % – when used with the LIKE operator, the percentage sign denotes one or more characters zero.
  2. _ – and underscore signifies a single character.

We can express the syntax for the like operator as shown below:

SELECT col1, col2, ...

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:

create table employees (

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

('Meghan Edwards', '[email protected]', 'Game Development', '2016-06-22', TRUE, 'DEV-G1'),

('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', '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', '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:

select * from employees

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:

select * from employees

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:

select * from employees

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:

select * from employees

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:

select * from employees

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:

select * from employees

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!!

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list