What are SQL Wildcards?

SQL wildcards are special characters used with the LIKE operator to search for patterns in text values. They are useful when you know only part of the value you want to find.

In SQLite, the main wildcards used with LIKE are % and _. SQLite also provides the GLOB operator with its own pattern characters.

Common SQL Wildcards

The % Wildcard

The percent sign (%) represents zero or more characters. In this example, the pattern finds product names that start with Smart.

WITH products(product_name) AS (
  VALUES
    ('Smartphone'),
    ('Smartwatch'),
    ('Laptop'),
    ('Tablet')
)
SELECT product_name
FROM products
WHERE product_name LIKE 'Smart%';

% at the Beginning

Placing % before the pattern finds values that end with the specified text.

WITH customers(email) AS (
  VALUES
    ('alex@example.com'),
    ('maria@example.com'),
    ('john@gmail.com'),
    ('sara@yahoo.com')
)
SELECT email
FROM customers
WHERE email LIKE '%@example.com';

% on Both Sides

Placing % on both sides searches for the specified text anywhere inside the value.

WITH books(title) AS (
  VALUES
    ('Database Basics'),
    ('Learning Python'),
    ('Advanced Database Design'),
    ('Web Development')
)
SELECT title
FROM books
WHERE title LIKE '%Database%';

The _ Wildcard

The underscore (_) represents exactly one character. Here, the pattern A_5 matches three-character values beginning with A and ending with 5.

WITH products(code) AS (
  VALUES
    ('A15'),
    ('A25'),
    ('AB5'),
    ('A125'),
    ('B15')
)
SELECT code
FROM products
WHERE code LIKE 'A_5';

Multiple _ Wildcards

Multiple underscore characters can represent multiple individual characters.

WITH users(username) AS (
  VALUES
    ('admin'),
    ('adams'),
    ('adrian'),
    ('adam'),
    ('editor')
)
SELECT username
FROM users
WHERE username LIKE 'ad___';

Combining % and _

You can combine both wildcards in one pattern. The underscore matches one character while the percent sign matches zero or more characters.

WITH products(product_name) AS (
  VALUES
    ('Camera Pro'),
    ('Camera X'),
    ('Camara Plus'),
    ('Laptop Pro'),
    ('Camera Ultra')
)
SELECT product_name
FROM products
WHERE product_name LIKE 'Cam_ra%';

Wildcard with Numbers

Wildcards can also search text values containing numbers, such as product codes.

WITH products(product_code) AS (
  VALUES
    ('PRD-2001'),
    ('PRD-2002'),
    ('PRD-3010'),
    ('ITEM-2005')
)
SELECT product_code
FROM products
WHERE product_code LIKE 'PRD-20%';

NOT LIKE with Wildcards

NOT LIKE returns values that do not match the specified pattern.

WITH products(product_name) AS (
  VALUES
    ('Old Camera'),
    ('New Camera'),
    ('Old Laptop'),
    ('New Laptop')
)
SELECT product_name
FROM products
WHERE product_name NOT LIKE 'Old%';

Wildcards with AND

Wildcard conditions can be combined with other conditions using AND.

WITH products(product_name, price) AS (
  VALUES
    ('Smartphone', 800),
    ('Smartwatch', 300),
    ('Smart TV', 1200),
    ('Laptop', 900)
)
SELECT product_name, price
FROM products
WHERE product_name LIKE 'Smart%'
AND price < 1000;

Wildcards with OR

The OR operator allows multiple wildcard patterns to be searched.

WITH customers(city) AS (
  VALUES
    ('New York'),
    ('New Delhi'),
    ('Los Angeles'),
    ('London'),
    ('Mumbai')
)
SELECT city
FROM customers
WHERE city LIKE 'New%'
OR city LIKE 'Los%';

SQLite GLOB Operator

SQLite provides the GLOB operator for pattern matching. Unlike LIKE, GLOB uses Unix-style wildcard characters. The * character matches zero or more characters.

WITH products(product_name) AS (
  VALUES
    ('Phone Basic'),
    ('Phone Pro'),
    ('Tablet Pro'),
    ('Laptop')
)
SELECT product_name
FROM products
WHERE product_name GLOB 'Phone*';

GLOB with ? Wildcard

With SQLite’s GLOB, the question mark (?) matches exactly one character.

WITH products(code) AS (
  VALUES
    ('A15'),
    ('A25'),
    ('AB5'),
    ('B15')
)
SELECT code
FROM products
WHERE code GLOB 'A?5';

Wildcard Patterns at a Glance

Advantages of SQL Wildcards

• Search for values when the exact text is unknown.
• Make text searches more flexible.
• Search for prefixes, suffixes, or text within a value.
• Can be combined with AND, OR, and NOT LIKE.
• SQLite supports LIKE and GLOB pattern matching.

Best Practices

• Use % for zero or more characters.
• Use _ for exactly one character with LIKE.
• Use * and ? with SQLite’s GLOB operator.
• Use specific patterns when possible to make searches more precise.
• Use LIKE when you want standard SQL-style wildcard matching.