String Functions and Pattern Matching

Every value so far in the SQL for Beginners series arrived ready to use: names to sort, statuses to filter. Chapter 17 of this 24-chapter series opens the text up — measuring names, cutting out parts, joining pieces together, and searching with patterns rather than exact values, plus an honest map of which function belongs to which database engine.

Measuring, cutting and trimming

Text lives in TEXT columns (the SQL standard’s VARCHAR is the same idea), and the basic verbs are what other languages already taught you. LENGTH counts characters, UPPER and LOWER change case, SUBSTR slices (positions start at 1, not 0), and TRIM removes spaces from one or both ends:

SELECT first_name,
       LENGTH(first_name) AS len,
       UPPER(first_name) AS shout,
       SUBSTR(city, 1, 3) AS city_head
FROM customers
ORDER BY id;

Expected output:

first_name | len | shout | city_head
Aarav | 5 | AARAV | Mum
Priya | 5 | PRIYA | Del
Lena | 4 | LENA | Ber
Marc | 4 | MARC | Tor
Wei | 3 | WEI | Sin
Amara | 5 | AMARA | Nai

SUBSTR(city, -3) would count from the other end, and SUBSTR(city, 2) drops the first character. Whitespace from imports and forms is invisible until it breaks a comparison, so trim at the boundary:

SELECT '[' || TRIM('   Berlin   ') || ']' AS trimmed,
       '[' || RTRIM('Toronto   ') || ']' AS rtrimmed;

Expected output: [Berlin] | [Toronto] — the brackets make the removed spaces visible. SQLite has no LEFT() or RIGHT(); use SUBSTR(s, 1, 4) and SUBSTR(s, -4) instead, which is the first sign that string functions are where dialects diverge most.

Counting has one subtlety worth fixing early: LENGTH() counts characters, not bytes, and a negative start counts from the other end — SUBSTR('Singapore', -4) returns pore, while LENGTH('Ochieng') is 7.

Joining strings: pipes and concat()

The || operator glues values together, and it doubles as the series’ favourite display trick — labels built straight from columns:

SELECT first_name || ' (' || country || ')' AS labelled
FROM customers
ORDER BY id;

Expected output:

labelled
Aarav (India)
Priya (India)
Lena (Germany)
Marc (Canada)
Wei (Singapore)
Amara (Kenya)

Concatenation composes with everything around it: UPPER(first_name || ' ' || last_name) runs the inner expression first, because any string-producing expression can be nested wherever another is accepted — the same rule that let TRIM sit inside the pipes just now.

Three engine notes, all learned the hard way by somebody. In MySQL, || means logical OR unless the server runs with PIPES_AS_CONCAT enabled, so concatenate with CONCAT() there. Nulls behave differently too: 'a' || NULL yields NULL (one missing piece ruins the whole label), while concat('a', NULL) returns 'a' in SQLite 3.44+ and PostgreSQL because that function skips nulls — but MySQL’s CONCAT() returns NULL. When a column might be missing, wrap it: first_name || ' (' || COALESCE(city, 'unknown') || ')'.

LIKE: the two wildcards

LIKE compares a value against a pattern built from exactly two wildcards: % matches any run of characters (including none), and _ matches exactly one. Start with names that begin with “A”:

SELECT first_name, last_name
FROM customers
WHERE first_name LIKE 'A%'
ORDER BY id;

Expected output:

first_name | last_name
Aarav | Sharma
Amara | Ochieng

The pattern can sit anywhere. Find every title containing the letters “at”:

SELECT name FROM products WHERE name LIKE '%at%' ORDER BY id;

Expected output:

name
The Silent Patient
The Very Hungry Caterpillar
Matilda

Anchors matter just as much: '_ech' insists on exactly four characters ending in “ech”, so this time it returns exactly two rows — the shop’s Tech titles:

SELECT name FROM products WHERE category LIKE '_ech' ORDER BY id;

Expected output: Clean Code and Python Crash Course — Tech is exactly four characters long and ends in “ech”; Kids matches the length but not the ending. Counting matches is the same idea in the SELECT list:

SELECT COUNT(*) AS all_of_them
FROM customers
WHERE email LIKE '%@example.com';

Expected output: 6 — every customer in the sample. What if a value genuinely contains % or _? Introduce an escape character with the ESCAPE clause: ! turns the next character literal.

SELECT 'discount 50%' AS note
WHERE 'discount 50%' LIKE '%50!%%' ESCAPE '!';

Expected output: one row, discount 50%. Read the pattern as “anything, then 50, then a literal % (written !%), then anything” — without the escape the trailing % would swallow everything and match far more than intended.

Patterns compose with each other as well: 'A%a' demands a name beginning with “A” and ending with “a”, which in this sample matches Amara alone — Aarav finishes on v — while '%a' loosens the start completely.

Case sensitivity: LIKE, GLOB and ILIKE

Databases disagree about case, and pretending otherwise is how queries break when they move. SQLite’s LIKE folds ASCII case by default ('a' LIKE 'A' is true), its GLOB operator never does, and PostgreSQL’s LIKE is strict — that is what ILIKE is for. Ask both operators directly:

SELECT 'a' LIKE 'A' AS like_ci, 'a' GLOB 'A' AS glob_cs;

Expected output:

like_ci | glob_cs
1 | 0

So the current rules are: SQLite — LIKE case-insensitive for ASCII only (accented letters are compared exactly), GLOB always case-sensitive with * and ? wildcards; MySQL — LIKE follows the column’s collation, which is usually case-insensitive; PostgreSQL — LIKE sensitive, ILIKE insensitive. When a search must behave the same everywhere, fold both sides yourself:

SELECT LOWER('AARAV SHARMA') AS folded, UPPER('singapore') AS shouted;

Expected output: aarav sharma | SINGAPORE — and in a real query the same habit reads WHERE LOWER(name) = LOWER('matilda'), which compares fairly on every engine.

Positions, replacement and formatting

INSTR returns the 1-based position of a substring, or 0 when it is absent — the non-pattern way to ask “does it contain…?”. REPLACE swaps every occurrence, which is how URLs and filenames are built from titles:

SELECT INSTR('Python Crash Course', 'Crash') AS position;

Expected output: 8 — the match starts at the eighth character. Combining three functions makes a slug generator:

SELECT REPLACE(LOWER(name), ' ', '_') AS slug FROM products ORDER BY id;

Expected output: six rows, starting the_silent_patient, the_very_hungry_caterpillar, clean_code, then python_crash_course, matilda and robotics_for_kids. SQLite’s printf() then formats values for display:

SELECT printf('Rs. %.2f', price) AS display,
       printf('%s (%d%% off)', name, 10) AS promo
FROM products WHERE id = 1;

Expected output:

display | promo
Rs. 499.00 | The Silent Patient (10% off)

PostgreSQL spells similar things FORMAT() and TO_CHAR(); MySQL uses FORMAT() for numbers and CONCAT() for joining.

Combining them is the pattern: INSTR returns 0 rather than NULL when the substring is simply missing, so WHERE INSTR(name, 'Crash') > 0 behaves like LIKE '%Crash%' without ever confusing a miss with a null.

One job, three dialects

Memorise the mapping once and porting a query stops being archaeology:

The pattern search most worth remembering: LIKE '%term%' matches anything containing term in every one of these engines, wildcards and all — the part that differs is only what surrounds it.

Indexes aside, the portable core is small: ||, SUBSTR, LENGTH, UPPER/LOWER, TRIM and LIKE behave alike in all three engines once case rules are pinned down — everything else in the table above deserves a quick check before it ships.

Common mistakes

• Leading wildcards on big tables — LIKE '%ing' cannot use an index and scans every row; prefix patterns ('C%') can, which chapter 19 turns into a performance win.
• Forgetting that _ matches exactly one character — '_ech' demands four characters while '%ech' allows any length; count the characters in your pattern before running it.
• Assuming case rules travel — a query proven on SQLite’s forgiving LIKE can fail in PostgreSQL, whose LIKE is case-sensitive; use ILIKE or fold both sides with LOWER().
• Using || in MySQL — without PIPES_AS_CONCAT it is logical OR, so strings come back as 0 or 1; write CONCAT() instead.
• Matching literal % or _ without ESCAPE — the wildcard silently matches more than the text you meant to find.
• Reaching for regex in SQLite — REGEXP is not built in and raises no such function; stick to LIKE, INSTR and GLOB.

Practice checklist

1. List every customer with LENGTH(first_name), sorted longest first, and tie-break with id so the result is stable.
2. Find the products whose names contain the letter “o” with LIKE '%o%' — expect three titles — then get the same answer with INSTR.
3. Prove the case rules: run SELECT 'a' LIKE 'A', 'a' GLOB 'A' on SQLite, then read what PostgreSQL and MySQL would say about each.
4. Build the slug query for products, then extend it so a product with a comma or dash would still be safe — note which function you need.
5. Reproduce the ESCAPE example, then find the row that fails when you delete the ! characters, and explain why.

What's next?

Text is only one of the value types waiting to be shaped. Next, tame the other two — arithmetic, dates and branching logic with Dates, Numbers and CASE Expressions.