SQL Pattern Matching Without the Pain: LIKE, Wildcards, and the Gotchas Nobody Warns You About

Jitendra Suryavanshi — Fri, 30 Oct 2026 04:08:57 +0000

A practical guide to searching text in SQL using LIKE, wildcards, SUBSTRING and LOWER, with real examples and the mistakes that cost you hours.

Every app I've worked on eventually hits the same moment: someone types a few letters into a search box and expects the right row to show up.

Behind that tiny feature sits one of the oldest tools in SQL: pattern matching. It looks simple, but it has quirks that silently return wrong results, or make a fast query crawl.

In this post I'll walk through pattern matching using a small Employees table, then cover the gotchas I wish someone had told me earlier.

The setup

CREATE TABLE Employees (
  EmpID INT PRIMARY KEY,
  Name VARCHAR(50),
  Department VARCHAR(50),
  Email VARCHAR(50)
);

INSERT INTO Employees (EmpID, Name, Department, Email) VALUES
  (1, 'Alice Johnson', 'HR',      'alice.johnson@example.com'),
  (2, 'Bob Smith',     'IT',      'bob.smith@example.com'),
  (3, 'Charlie Brown', 'Finance', 'charlie.brown@example.com'),
  (4, 'Diana Prince',  'HR',      'diana.prince@example.com'),
  (5, 'Eve Adams',     'IT',      'eve.adams@example.org');

Five rows is enough. The goal is to understand the patterns, not to stress the database.

The two wildcards you need

LIKE understands exactly two special characters:

Wildcard	Meaning
`%`	any number of characters (including zero)
`_`	exactly one character

Everything else is matched literally. Once that clicks, most of the rest is just where you place them.

1. Starts with, ends with, contains

The position of % tells SQL what you mean.

-- Starts with 'A'
SELECT Name FROM Employees WHERE Name LIKE 'A%';
-- Alice Johnson

-- Ends with 'son'
SELECT Name FROM Employees WHERE Name LIKE '%son';
-- Alice Johnson

-- Contains 'IT'
SELECT Name, Department FROM Employees WHERE Department LIKE '%IT%';
-- Bob Smith (IT), Eve Adams (IT)

A quick way to remember it: the % goes on the side where you don't care what comes next. 'A%' means "I care about the start, anything can follow."

2. Filtering by domain

Pattern matching is great for light data cleaning, like finding emails from one domain:

SELECT Name, Email
FROM Employees
WHERE Email LIKE '%@example.com';

This returns Alice, Bob, Charlie and Diana. Eve is left out because her address ends in .org. Including the @ in the pattern matters. Without it, '%example.com' would also match something like bob@notexample.com.

3. "Second letter is v": two ways

This one is a nice puzzle because there are two valid solutions.

Using a function:

SELECT Name FROM Employees WHERE SUBSTRING(Name, 2, 1) = 'v';

Using the single-character wildcard:

SELECT Name FROM Employees WHERE Name LIKE '_v%';

Both return Eve Adams. Read the second one as: "one any character, then v, then anything."

I prefer the LIKE version. It's shorter, and it reads like the pattern it describes. SUBSTRING is better when you need to compare the extracted piece against something more complex.

4. Case-insensitive matching

Say you want everyone in HR, but your data might contain HR, hr or Hr:

SELECT Name, Department
FROM Employees
WHERE LOWER(Department) LIKE '%hr%';

Lowercasing the column and writing the pattern in lowercase makes the match independent of how the data was typed. You get Alice Johnson and Diana Prince.

The gotchas

Gotcha 1: `LIKE` isn't case-sensitive everywhere (or case-insensitive everywhere)

This is the biggest source of "works on my machine" bugs.

In PostgreSQL, LIKE is case-sensitive. It also has ILIKE for case-insensitive matching.
In MySQL and SQL Server, case sensitivity depends on the column's collation, and the common defaults are case-insensitive.
In SQLite, LIKE is case-insensitive for plain ASCII letters by default.

That affects the '%IT%' query above. On a case-insensitive setup, it would also match a department named Facilities, because "Facilities" contains "it". If exact case matters, don't rely on the default. Be explicit.

Gotcha 2: Searching for a literal `%` or `_`

What if you need to find a value like 50%_off? The wildcards get in your way. Use ESCAPE:

SELECT * FROM Promotions
WHERE Code LIKE '50\%\_off' ESCAPE '\';

The backslash tells SQL to treat the next character literally. Any time user input goes into a LIKE pattern, remember to escape these characters, or users can accidentally (or deliberately) match far more than they intended.

Gotcha 3: A leading `%` can make queries slow

WHERE Name LIKE 'Ali%'   -- can use a normal index
WHERE Name LIKE '%ice'   -- usually cannot

With a pattern that starts with a fixed prefix, the database can jump into a regular index. With a leading wildcard, it generally has to scan every row. On five rows you'll never notice. On five million, you will.

If you need fast "contains" search on large tables, look at full-text search, or trigram indexes in PostgreSQL, instead of leaning on LIKE '%...%'.

Gotcha 4: `NULL` never matches

SELECT * FROM Employees WHERE Email LIKE '%@example.com';

A row where Email is NULL is not returned, and it won't be returned by NOT LIKE either. Comparisons with NULL evaluate to unknown, not true. If you need those rows, handle them with IS NULL or COALESCE.

Which tool should I use?

You want to...	Reach for
Match a simple shape (starts, ends, contains)	`LIKE`
Match by position	`_` or `SUBSTRING`
Ignore case reliably	`LOWER()` / `UPPER()` (or `ILIKE` in PostgreSQL)
Match complex patterns	Regex (`REGEXP` in MySQL, `~` in PostgreSQL)
Search large text quickly	Full-text search

Wrapping up

Pattern matching is one of those topics that feels too basic to study, until a search feature returns the wrong rows in production. The core ideas are small:

% means "anything", _ means "exactly one character".
Where you place the wildcard defines the meaning.
Case sensitivity depends on your database, so check it.
Leading wildcards are fine for small data and costly for big data.

Try the queries above on your own data and break them on purpose. Adding a lowercase hr row or a NULL email will teach you more than reading about it.

If this helped, drop a comment with the weirdest LIKE bug you've run into. I'd love to hear it.

DEV Community: Jitendra Suryavanshi