SQL Not Like

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.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

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.

SQL Not Like 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;

SQL Not Like Syntax

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.

Frequently Asked Questions (FAQs)

When should I use the SQL NOT LIKE operator instead of LIKE?

Use NOT LIKE when you need to exclude rows whose column values match a specific pattern. While LIKE retrieves rows that conform to the pattern, NOT LIKE does the opposite—returning only the values that don’t match. This is handy for tasks such as finding customer names that do not contain “Smith” or email addresses that do not end with “.edu”.

Which wildcards work with NOT LIKE to build advanced filters?

The NOT LIKE operator understands the same wildcards as LIKE. The percent sign (%) substitutes for any sequence of characters, the underscore (_) matches a single character, and square brackets ([]) let you specify a character set or range. Combining these with NOT LIKE lets you write nuanced exclusions—for example WHERE phone NOT LIKE '___-%' to ignore three-digit area codes.

How does Galaxy help me write and debug NOT LIKE queries faster?

Galaxy’s context-aware AI copilot autocompletes patterns, suggests wildcard usage, and flags inefficient filters as you type. Its lightning-fast editor and inline results let you iterate on NOT LIKE clauses in real time, while Collections make it easy to share verified exclusion queries with teammates—no more pasting SQL snippets into Slack.