SQL LIKE Operator – Wildcards, ESCAPE, ILIKE, and Examples | DbSchema

Table of Contents

1. What the SQL LIKE operator does
2. LIKE operator syntax
3. Wildcard characters reference
4. Pattern matching examples
5. NOT LIKE
6. ILIKE and case-insensitive search
7. ESCAPE for literal % and _
8. LIKE vs = vs REGEXP
9. Database support and performance tips
10. Run LIKE queries visually in DbSchema
11. FAQ
12. Conclusion

The SQL LIKE operator filters rows by matching text against a pattern. It is one of the most common tools for prefix, suffix, and substring search, especially inside a WHERE clause.

Good LIKE tutorials need more than two wildcard examples. People also want to know when to use NOT LIKE, how ILIKE works, how to search for a literal %, and why some LIKE queries use indexes while others do not. This guide covers those gaps and shows how DbSchema can help you test pattern searches against real tables.

What the SQL LIKE operator does

LIKE checks whether a text value matches a pattern. The pattern can contain:

• normal characters that must appear exactly
• % for zero or more characters
• _ for exactly one character

You will usually see LIKE with SQL SELECT and the SQL WHERE Clause, because it is a filtering operator rather than a standalone command.

LIKE operator syntax

SELECT column1, column2
FROM table_name
WHERE column_name LIKE 'pattern';

The database evaluates the pattern from left to right and returns the row if the text value matches. If you want to exclude a pattern, use NOT LIKE.

Wildcard characters reference

For a broader overview of pattern symbols, read SQL Wildcard Characters.

Pattern matching examples

Sample table employees:

Names that start with a letter

SELECT *
FROM employees
WHERE name LIKE 'J%';

Names that end with a string

SELECT *
FROM employees
WHERE name LIKE '%son';

Result: Mike Johnson

Names that contain text anywhere

SELECT *
FROM employees
WHERE name LIKE '%Davis%';

Result: Emily Davis

Single-character wildcard with _

SELECT *
FROM employees
WHERE name LIKE '_a%';

This matches names where the second character is a.

Combining wildcards

SELECT *
FROM employees
WHERE name LIKE 'J_n%';

This pattern means:

• first character must be J
• second character can be anything
• third character must be n
• anything can follow

NOT LIKE

NOT LIKE excludes rows that match a pattern:

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

This is useful for finding:

• records outside your expected naming format
• suspicious email domains
• values that do not start with a required prefix

You can combine multiple NOT LIKE conditions with AND when you need tighter filtering.

ILIKE and case-insensitive search

Standard LIKE can be case-sensitive or case-insensitive depending on the database and collation. PostgreSQL adds ILIKE, which performs case-insensitive matching:

SELECT *
FROM products
WHERE name ILIKE 'apple%';

If your database does not support ILIKE, normalize the text:

SELECT *
FROM products
WHERE LOWER(name) LIKE 'apple%';

That pattern works in MySQL, SQL Server, SQLite, and Oracle. The tradeoff is that wrapping the column in a function can reduce index use unless you create a functional index.

ESCAPE for literal % and _

If the data itself contains % or _, you need an escape character so the database treats them as literal characters instead of wildcards.

SELECT *
FROM discounts
WHERE description LIKE '%50\%%' ESCAPE '\';

That query matches values containing the literal text 50%.

SELECT *
FROM files
WHERE filename LIKE '%\_%' ESCAPE '\';

That query finds filenames containing an underscore. A backslash is the most common escape character, but any single character can be used.

LIKE vs = vs REGEXP

Competitor pages often stop at the basic syntax. In real work, people usually want to know which operator fits the search problem.

Use = when you know the complete value. Use LIKE when users type only part of the value. Use regex only when the pattern rules are too complex for % and _.

Database support and performance tips

Can LIKE use an index?

Yes, but only for certain patterns:

-- Can usually use a normal index
WHERE name LIKE 'Smith%'

-- Usually cannot use a normal index efficiently
WHERE name LIKE '%Smith%'

A leading wildcard forces the database to inspect far more rows. For better performance on infix search:

• use prefix patterns when possible
• consider full-text search for large text fields
• in PostgreSQL, consider trigram indexes for %text% search

When you want to inspect the relevant indexes before tuning the query, DbSchema makes that easier because the table structure and indexes are visible in the schema documentation.

Run LIKE queries visually in DbSchema

DbSchema helps when you need to test pattern matching against real data instead of toy examples.

1. Connect using the PostgreSQL JDBC driver, MySQL JDBC driver, or SQL Server JDBC driver.
2. Open the table in the Relational Data Explorer to inspect realistic values first.
3. Switch to the Query Builder or SQL editor and add a LIKE or NOT LIKE filter.
4. Run the query and compare the returned rows immediately.

DbSchema is especially helpful when you need to toggle between visual filtering and raw SQL, or when you want to document searchable columns for teammates.

FAQ

Is SQL LIKE case-sensitive?
It depends on the database and collation. PostgreSQL LIKE is case-sensitive by default, while MySQL often behaves case-insensitively with common collations. PostgreSQL ILIKE is the usual case-insensitive option.

What is the difference between LIKE and =?
= performs exact equality matching. LIKE allows wildcard matching with % and _. If your pattern has no wildcards, = is usually clearer and often faster.

Can I use LIKE with numbers or dates?
You can, but the database must convert the value to text first. It is usually better to use numeric comparisons or date ranges instead.

How do I match a literal percent sign or underscore? Use the ESCAPE clause, for example LIKE '%\%%' ESCAPE '\' or LIKE '%\_%' ESCAPE '\'.

Does LIKE support regular expressions?
No. LIKE supports only simple wildcard matching. For advanced patterns, use regex support such as REGEXP, RLIKE, or PostgreSQL regex operators.

Conclusion

The SQL LIKE operator is simple to start with, but the details around case sensitivity, literal wildcard characters, and indexing make a big difference in real applications. Learn the wildcard patterns first, then focus on when LIKE, ILIKE, or a more advanced search tool is the better choice.

For related lessons, continue with SQL Wildcard Characters, SQL WHERE Clause, and SQL SELECT.