SQL chapters

Chapter 11 of 17

Searching and transforming text

Try this in our SQLite Compiler →

The idea

SQLite concatenates strings with ||. LIKE performs pattern matching: % means any sequence and _ means one character. Its case behaviour has SQLite-specific rules and is not universal Unicode case folding. Use bound parameters when search text comes from users.

Worked example

WITH names(name) AS (VALUES ('Asha'), ('Ravi'), ('Anil'))
SELECT name, upper(name) AS label FROM names WHERE name LIKE 'A%' ORDER BY name;

What to check

Anil and Asha match the prefix pattern. A percent sign in a pattern is not necessarily a literal percent sign.

Practice task

Search for a four-character name starting with A using underscores. Explain the difference from A%.

Check your understanding

Explain the result in your own words before changing the example. Change one thing at a time, compare the result with your prediction, and keep the version that demonstrates the concept clearly.

Try it yourself

Output
Run your project to see output here.
Ctrl/Cmd+Enter runs · Ctrl/Cmd+S downloads the project · Local auto-save is always on