SQL Wildcards

Here is a clear and complete guide to SQL Wildcards, how they work, and how to use them effectively in pattern matching queries.

✅ What Are SQL Wildcards?

Wildcards are special characters used with the LIKE (and sometimes NOT LIKE) operator to search for patterns in text fields.

🔑 Main SQL Wildcards

1. % — Match zero or more characters

Most commonly used wildcard.

Examples:


 

2. _ — Match exactly one character

Useful for fixed-length patterns.

Examples:


 

🔧 Escaping Wildcards

If you need to search for % or _ literally:


 

🔥 Wildcards in Different Database Engines

MySQL

  • % and _ work normally

  • Case-insensitivity depends on collation (most are case-insensitive)

PostgreSQL

  • % and _ are case-sensitive

  • Use ILIKE for case-insensitive matches:

WHERE name ILIKE '%john%';

SQL Server

  • Same wildcards (%, _)

  • Also supports bracket expressions:


 

📦 Additional Wildcards (SQL Server Only)

[ ] — Character range or list

WHERE code LIKE 'A[0-9]B' -- A + any digit + B

[^ ] — NOT in list or range

WHERE letter LIKE '[^A-Z]' -- not a letter A–Z

⏱ Performance Tips

✔ Avoid leading % when possible (%text%) — prevents index usage
✔ For large text searches, use:

  • FULL TEXT SEARCH (MySQL, PostgreSQL, SQL Server)

  • GIN indexes (PostgreSQL)

✔ Use lower()/UPPER() or ILIKE for case-insensitive search when needed

✔ Real-World Examples

Find emails from Gmail


 

Find items where SKU starts with “AB-“


 

Find posts containing the word “error”


 

You may also like...