SQL Wildcards

SQL Tutorial

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:

SQL Server

  • Same wildcards (%, _)

  • Also supports bracket expressions:

 Additional Wildcard (SQL Server Only)

[ ] — Character range or list

[^ ] — NOT in list or range

 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...