SQL Wildcard Characters – LIKE Patterns, ESCAPE Rules, and Examples | DbSchema

Table of Contents

1. What SQL wildcards are
2. Wildcard characters reference table
3. Common pattern cookbook
4. The % wildcard – zero or more characters
5. The _ wildcard – exactly one character
6. The [] wildcard – character set (SQL Server and Access)
7. Combining wildcards
8. NOT LIKE – exclude matching patterns
9. Escaping wildcard characters
10. Case sensitivity: LIKE vs ILIKE
11. Wildcards by database
12. LIKE vs REGEXP vs full-text search
13. Performance tips for wildcard queries
14. Use wildcards in DbSchema
15. FAQ
16. Conclusion

SQL wildcard characters are special symbols used with the LIKE operator to match patterns in text. They help you find rows based on partial values, such as names that start with A, product codes that end with -XL, or strings that contain a literal %.

Wildcard searches appear in search boxes, audit filters, data-cleanup jobs, and reporting queries. They also become much easier to validate when you can preview real column values in DbSchema before deciding on the final pattern.

What SQL wildcards are

A wildcard acts as a placeholder for one or more unknown characters inside a string. Wildcards are normally used with LIKE or NOT LIKE.

SELECT *
FROM products
WHERE name LIKE 'Shirt%';

This returns every product whose name starts with Shirt.

Sample table used throughout this guide:

CREATE TABLE Products (
    product_id   INT PRIMARY KEY,
    name         VARCHAR(100),
    category     VARCHAR(50),
    price        DECIMAL(10, 2)
);

INSERT INTO Products VALUES
    (1,  'Shirt-Blue',   'Clothing',     19.99),
    (2,  'Shirt-Red',    'Clothing',     21.99),
    (3,  'Shorts-XL',    'Clothing',     34.99),
    (4,  'Shoes',        'Footwear',     89.99),
    (5,  'Hat',          'Accessories',  12.99),
    (6,  'Belt-M',       'Accessories',  24.99),
    (7,  '10% Discount', 'Promotions',    0.00);

For a broader introduction to pattern matching syntax, see SQL LIKE Operator.

Wildcard characters reference table

Common pattern cookbook

This is the quickest way to map a search intent to a LIKE pattern:

If you are building a search UI, parameterize the pattern in your application rather than concatenating raw user input.

The % wildcard – zero or more characters

% matches zero, one, or many characters. It is the most common wildcard.

Starts with

SELECT * FROM Products WHERE name LIKE 'Shirt%';

Ends with

SELECT * FROM Products WHERE name LIKE '%-XL';

Contains

SELECT * FROM Products WHERE name LIKE '%oes%';

Important behavior

% can also match an empty string. So LIKE 'Hat%' matches Hat as well as Hat-Blue.

The _ wildcard – exactly one character

_ matches exactly one character.

SELECT * FROM Products WHERE name LIKE 'Belt-_';

That matches Belt-M, Belt-L, or Belt-X, but not Belt-XL.

Combine % and _

SELECT * FROM Products WHERE name LIKE 'S____%';

This matches names that start with S and have at least five characters.

The [] wildcard – character set (SQL Server and Access)

[] is supported in SQL Server and Access. It matches one character from the listed set or range.

SELECT * FROM Products WHERE name LIKE '[BH]%';

Character range

SELECT * FROM Products WHERE name LIKE '[A-S]%';

Negation with [^]

SELECT * FROM Products WHERE name LIKE '[^S]%';

In MySQL and PostgreSQL, [] is treated as literal text inside LIKE. For character classes in those databases, use regular-expression features instead.

Combining wildcards

Wildcards can be combined for more specific patterns:

SELECT * FROM Products WHERE name LIKE 'S_i%';

This matches values that start with S, have any one character in the second position, then i.

SELECT * FROM Products WHERE name LIKE '%-%';

This finds any product name that contains a hyphen.

NOT LIKE – exclude matching patterns

Use NOT LIKE when you want rows that do not match the pattern:

SELECT * FROM Products WHERE name NOT LIKE 'Shirt%';

This is useful for cleanup reports, exception lists, and validation queries.

Escaping wildcard characters

If the data contains literal % or _ characters, escape them so they are treated as normal text.

Standard SQL ESCAPE clause

SELECT *
FROM Products
WHERE name LIKE '%\%%' ESCAPE '\';

That finds rows containing a real percent sign, such as 10% Discount.

Literal underscore example

SELECT *
FROM product_codes
WHERE code LIKE '%\_%' ESCAPE '\';

Database-specific notes

Case sensitivity: LIKE vs ILIKE

Case sensitivity varies by database and collation.

PostgreSQL ILIKE example

SELECT * FROM Products WHERE name ILIKE 'shirt%';

If you need row filtering and sorting around these patterns, review SQL WHERE Clause and SQL ORDER BY.

Wildcards by database

Understanding these differences matters when you move SQL between systems or test patterns locally before shipping them to production.

LIKE vs REGEXP vs full-text search

LIKE is not the answer to every text-search problem.

If you only need simple matching, LIKE is easier and more portable. If you need complex pattern logic, use regex support. If you need ranked search over large text, use database full-text search or a dedicated search engine.

Performance tips for wildcard queries

1. Avoid leading wildcards — LIKE '%term' usually prevents normal index use
2. Prefer trailing wildcards — LIKE 'term%' can often use a B-tree index
3. Normalize case intentionally — case-insensitive searches may benefit from expression indexes such as LOWER(column)
4. Escape user-supplied % and _ characters when they should be treated literally
5. Use the right tool — switch to regex or full-text search when LIKE becomes awkward

For debugging, it helps to preview the actual values in the table before deciding on the pattern. DbSchema makes that step much faster than guessing from schema names alone.

Use wildcards in DbSchema

DbSchema makes it easy to test wildcard queries interactively before they reach production code.

1. Connect to your database using the PostgreSQL JDBC driver, MySQL JDBC driver, or SQL Server JDBC driver
2. Open the SQL Editor and test LIKE, NOT LIKE, or ILIKE queries
3. Preview real column values in the table browser so you can design the right pattern
4. Switch to the Query Builder to add filters visually
5. Save the query or publish it through shared schema documentation

This is especially helpful for data-quality audits, search features, and cleanup jobs where one wildcard change can alter the result set dramatically.

For related reading, see SQL LIKE Operator, SQL SELECT, and SQL WHERE Clause.

FAQ

What is the difference between % and _ in SQL?

% matches zero or more characters. _ matches exactly one character.

Does % match an empty string?

Yes. That is why LIKE 'Hat%' matches Hat as well as longer strings that begin with Hat.

How do I search for a literal % or _ character?

Use the ESCAPE clause and prefix the wildcard with the chosen escape character, such as LIKE '%\%%' ESCAPE '\'.

Is LIKE case-sensitive in SQL?

It depends on the database and collation. PostgreSQL LIKE is case-sensitive; ILIKE is the case-insensitive alternative there.

Can I use [] wildcards in PostgreSQL or MySQL?

Not with LIKE. The [] syntax is mainly for SQL Server and Access.

Does a leading % prevent index use?

Usually yes. LIKE '%value%' often forces a scan because the database cannot jump to a specific index prefix.

Conclusion

SQL wildcard characters are essential for pattern-based filtering. % and _ work across major databases, ILIKE helps in PostgreSQL, and ESCAPE is how you search for literal wildcard characters safely.

Use DbSchema to inspect real values, test LIKE patterns interactively, and move from ad-hoc filtering to documented, repeatable queries with confidence.