CodeCapsule

SQL Wildcards

by

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:

WHERE name LIKE 'A%' -- starts with A
WHERE name LIKE '%son' -- ends with 'son'
WHERE name LIKE '%ohn%' -- contains 'ohn'

2. _ — Match exactly one character

Useful for fixed-length patterns.

Examples:

WHERE code LIKE 'A_3' -- A + any one char + 3
WHERE phone LIKE '555-___' -- 555- followed by any 3 characters

🔧 Escaping Wildcards

If you need to search for % or _ literally:

WHERE message LIKE '%error\_%' ESCAPE '\';

🔥 Wildcards in Different SQL 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:

WHERE name LIKE '[A-C]%' -- names starting with A, B, or C
WHERE name LIKE '[^A]%' -- NOT starting with A

📦 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

SELECT * FROM users
WHERE email LIKE '%@gmail.com';

Find items where SKU starts with “AB-“

SELECT * FROM items
WHERE sku LIKE 'AB-%';

Find posts containing the word “error”

SELECT * FROM logs
WHERE message LIKE '%error%';

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *