SQL Not Like

Galaxy Glossary

How do you filter data in SQL that doesn't match a specific pattern?

The `NOT LIKE` operator in SQL is used to select rows where a column value does not match a specified pattern. It's a powerful tool for filtering data based on criteria that don't fit a particular format or string. It's often used in conjunction with wildcards for more complex matching.

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

The `NOT LIKE` operator in SQL is a crucial component for filtering data that doesn't conform to a specific pattern. It's the opposite of the `LIKE` operator, which selects rows where a column value matches a specified pattern. This operator is particularly useful when you need to extract data that doesn't adhere to a particular format or string. For example, you might want to find all customer names that don't contain the substring 'Smith'. The `NOT LIKE` operator allows you to achieve this efficiently. It's often used in conjunction with wildcards like '%', '_', and '[ ]' to create more complex matching criteria. Understanding `NOT LIKE` is essential for creating precise queries that extract only the desired data from a database table. It's a fundamental part of data manipulation and retrieval in SQL.

Why SQL Not Like is important

The `NOT LIKE` operator is crucial for data filtering. It allows developers to extract specific data points that don't meet a certain pattern, which is essential for tasks like data analysis, reporting, and data cleaning. It's a fundamental tool for creating precise queries that target only the relevant data.

Example Usage


-- Sample tables
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    City VARCHAR(50)
);

CREATE TABLE NewCustomers (
    CustomerID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    City VARCHAR(50)
);

-- Insert some sample data into Customers
INSERT INTO Customers (CustomerID, FirstName, LastName, City) VALUES
(1, 'John', 'Doe', 'New York'),
(2, 'Jane', 'Smith', 'Los Angeles');

-- Insert some sample data into NewCustomers
INSERT INTO NewCustomers (CustomerID, FirstName, LastName, City) VALUES
(3, 'Peter', 'Jones', 'Chicago'),
(4, 'David', 'Lee', 'Houston');

-- MERGE statement
MERGE INTO Customers AS Target
USING NewCustomers AS Source
ON Target.CustomerID = Source.CustomerID
WHEN MATCHED THEN
UPDATE SET
    FirstName = Source.FirstName,
    LastName = Source.LastName,
    City = Source.City
WHEN NOT MATCHED THEN
INSERT (
    CustomerID,
    FirstName,
    LastName,
    City
)
VALUES (
    Source.CustomerID,
    Source.FirstName,
    Source.LastName,
    Source.City
);

SELECT * FROM Customers;
-- Clean up
DROP TABLE Customers;
DROP TABLE NewCustomers;

Common Mistakes

Forgetting to use the `NOT` keyword when you want to exclude matching rows.
Using incorrect wildcard characters, leading to unexpected results.
Not understanding the difference between `LIKE` and `NOT LIKE`.
Incorrectly using the `%` wildcard, potentially matching more than intended.

SQL Not Like

How do you filter data in SQL that doesn't match a specific pattern?

Description

Why SQL Not Like is important

Example Usage

Common Mistakes

Want to learn about other SQL terms?

With Cte In SQL

Where Vs Having SQL

Windows Functions In SQL