Searching text

How is LIKE different from = ?

The = operator asks whether two values are exactly the same. title = 'garden' only keeps a row whose whole title is the single word garden, and no book in the library is called that. When you search a catalogue, you almost never know the full text. You know a piece of it: a word in the title, the start of a surname, the letters at the front of a shelf code.

LIKE compares text against a pattern. A pattern is ordinary text with two special characters in it. The percent sign % stands for any run of characters, of any length, including no characters at all. The underscore _ stands for exactly one character. Everything else in the pattern must match literally.

The pattern always has to cover the whole value. That catches people out. title LIKE 'garden' behaves almost like =, because there is nothing in the pattern to soak up the rest of the title. The % signs are what make it a search.

SELECT title
FROM books
WHERE title LIKE '%garden%';

Where should the % go?

Where you put % decides which part of the text is free. Read the pattern from left to right and say it out loud: 'Garden%' is "starts with Garden, then anything". '%Garden' is "anything, then ends with Garden". '%Garden%' is "Garden somewhere". Each one gives a different list from the same table.

Run the three versions below one at a time against the books table. The starts-with version finds Garden Birds of Britain and Gardening on a Budget. The ends-with version finds The Secret Garden and The Walled Garden. The contains version finds all six, including Kitchen Garden Year and Night GARDEN Poems. Watch the Rows view while you do it: the WHERE step shows each title and whether the pattern kept it.

The contains version is the one people reach for by default, and it often matches more than you meant. Gardening on a Budget contains garden inside a longer word. If you want the word on its own, you have to spell out the spaces, for example '% garden %', and handle the start and end of the title separately. For real word search, databases have full-text search features; for a small catalogue, a careful LIKE is fine.

SELECT title
FROM books
WHERE title LIKE 'Garden%';

• 'Garden%': starts with Garden.
• '%Garden': ends with Garden.
• '%Garden%': Garden appears anywhere.
• 'G_rden%': G, any one character, rden, then anything.

What does the underscore match?

An underscore matches one character, no more and no fewer. It is useful when the shape of the text is fixed but one position varies. Shelf codes here are three letters, an underscore and a number, so 'COO_0_' finds the cookery shelves COO_05, COO_06 and COO_07.

The underscore has a side effect that surprises people the first time. In shelf_code LIKE 'NAT_%', the _ is a wildcard, so the pattern means NAT, any one character, then anything. NAT03 matches, because 0 counts as the one character. The Break it exercise below is built on exactly this bug.

SELECT title, shelf_code
FROM books
WHERE shelf_code LIKE 'COO_0_';

Does LIKE care about capital letters?

In SQLite, LIKE ignores case for the 26 English letters. '%garden%' matches Garden, GARDEN and garden. That is why Night GARDEN Poems turns up in the demo even though the task word is lowercase.

The rule stops at plain ASCII. Letters with accents, such as É and é, are compared exactly, so '%café%' will not match CAFÉ. If your data has accented names, store a lowercase copy or use LOWER() on both sides and test it.

Other databases differ, and this is where queries break when you move them. In PostgreSQL, LIKE is case-sensitive, and you write ILIKE for the case-insensitive version. In MySQL, it depends on the column's collation, which is usually case-insensitive. If an AI tool hands you ILIKE, that query was written for PostgreSQL and SQLite will report a syntax error.

SELECT title
FROM books
WHERE title LIKE '%GARDEN%';

How do you exclude matches with NOT LIKE?

Put NOT in front of LIKE to keep the rows that do not match. title NOT LIKE '%bird%' drops Garden Birds of Britain, Birdsong at Dawn and The Birdwatcher, and keeps everything else.

You can combine it with other conditions using AND and OR, the same way you did in the lesson on combining conditions. A common shape is a broad LIKE to find a group of rows and a NOT LIKE to remove a few you do not want. Keep the brackets clear when OR is involved, because AND is evaluated first.

One trap: a NOT LIKE pattern without % excludes almost nothing. title NOT LIKE 'bird' only drops a title that is exactly bird, so every book stays. If an exclusion seems to have no effect, check the wildcards first.

SELECT title, genre
FROM books
WHERE genre = 'Nature'
  AND title NOT LIKE '%bird%';

How do you search for a real % or _?

Sometimes the character you want to find is one of the wildcards. The cookery shelf has a book called Sourdough: 100% Rye, and the shelf codes all contain a real underscore. To match those characters literally, choose an escape character and tell SQLite about it with ESCAPE.

In LIKE '%100\%%' ESCAPE '\', the backslash says that the next character is literal. The first % is a wildcard, \% is a real percent sign, and the last % is another wildcard. SQLite has no default escape character, so without the ESCAPE clause the backslash is an ordinary character and the pattern will not do what you expect. Any single character works as the escape; backslash and the exclamation mark are common choices.

SELECT title
FROM books
WHERE title LIKE '%100\%%' ESCAPE '\';

What is GLOB and when would you use it?

GLOB is a SQLite operator that matches patterns the way a computer's file system does. It uses * for any run of characters and ? for one character, and it is case-sensitive. It also understands sets in square brackets, such as [0-9] for one digit.

That makes GLOB handy for checking the shape of codes. shelf_code GLOB 'NAT_[0-9][0-9]' finds Nature shelves that have a real underscore followed by two digits, because in GLOB the underscore has no special meaning. GLOB is SQLite only. In PostgreSQL you would reach for SIMILAR TO or a regular expression with ~.

SELECT title, shelf_code
FROM books
WHERE shelf_code GLOB 'NAT_[0-9][0-9]';

Why can a LIKE search be slow?

On a table of fifteen books, every query is instant. On a table of five million, the shape of the pattern makes a big difference. A database can use an index to jump straight to the rows that start with certain letters, the same way you open a dictionary near the right page. A pattern like 'Gard%' has a fixed start, so an index can help in principle.

A pattern that starts with %, such as '%garden%', has no fixed start. The database has to read every row and test it. That is called a full scan. In SQLite, an index only helps LIKE under specific settings (the column must use case-insensitive collation, or you turn on case-sensitive LIKE), so most everyday searches scan anyway. That is fine for small tables. When a search box on a big site feels sluggish, a leading % is one of the first things to look at. The lesson on indexes goes further.

What should you check in AI-written LIKE queries?

AI tools write LIKE queries constantly, and they tend to get the same few things wrong. Read each pattern and say in words what it matches before you trust it.

• Missing %: LIKE 'smith' behaves like =.
• % on one side only when the text could appear anywhere, or on both sides when you only wanted titles that start with the word.
• An _ in the search text that was meant literally, such as in product or shelf codes.
• ILIKE, which is PostgreSQL only; SQLite needs plain LIKE.
• User input pasted straight into a pattern. A search for 100% will behave like a wildcard unless the % is escaped, and pasting text into SQL at all is how SQL injection happens.

Field notes

• = needs an exact match, while LIKE matches a pattern.
• % stands for any run of characters, including none; _ stands for exactly one.
• '%garden%' finds the word anywhere, and 'Garden%' only at the start.
• In SQLite, LIKE ignores case for plain English letters A to Z.

Easy to get wrong

Without %, the pattern must match the whole title, so this acts like = and finds nothing.

Wildcards only work with LIKE or GLOB. With =, the % signs are just characters to compare.

The underscore is a one-character wildcard, so NAT03 sneaks in. Escape it when you mean a real underscore.

ILIKE exists in PostgreSQL. SQLite's LIKE already ignores case for English letters.

Questions people ask

Can I use LIKE on numbers?

Yes. SQLite turns the number into text first, so pages LIKE '2%' finds 204, 210, 240, 260 and 288. It works, but a range with BETWEEN is usually easier to read and can use an index.

How do I make LIKE case-sensitive in SQLite?

Use GLOB with * and ?, which is always case-sensitive. There is also a PRAGMA case_sensitive_like setting, but it is deprecated and changes every LIKE in the connection.

Can I match several patterns at once?

Join them with OR: title LIKE '%garden%' OR title LIKE '%bird%'. There is no LIKE IN (...) shortcut in SQLite.

Does LIKE match NULL?

No. A NULL title gives an unknown result for both LIKE and NOT LIKE, so the row is dropped either way. The next lesson covers missing values.