1. Strings

The Hook

A daily report joins user-entered emails to a marketing list. Twenty thousand records on each side. The query:

SELECT u.email FROM users u JOIN newsletter n ON n.email = u.email;

Returns 14,200 matches. Marketing complains: "we know there are at least 17,000 overlaps."

The bug is invisible in a list view. Only when you SELECT email, LENGTH(email) do you see it: the user-entered emails have leading/trailing whitespace (' [email protected]'), mixed casing ('[email protected]'), and a few have a stray Zero-Width Space (​) injected by a copy-paste from a fancy mail client. The newsletter list is normalised; the user input is not. The two strings look identical to the eye but are not equal under SQL's exact-match join.

The fix involves no schema change, just a tiny per-row computation:

SELECT u.email FROM users u
JOIN newsletter n ON LOWER(TRIM(n.email)) = LOWER(TRIM(u.email));

Three lines turn a partial-match query into a complete one. That's what string functions do — clean up data on the way through, normalise comparisons, repair the inconsistencies that creep into every column of human-entered text.

This chapter is about the string functions you'll reach for most often, the regex tier for when LIKE isn't expressive enough, and the dialect quirks that make portable string SQL slightly harder than it should be. By the end you'll know which function to reach for in each case, why function-on-column in WHERE blocks index use, and how to write defensive joins that survive whitespace and case mismatches.

Table of contents

1. The string-function catalogue
2. Concatenation: || and CONCAT
3. Case: LOWER, UPPER, INITCAP
4. Length and slicing: LENGTH, SUBSTRING, LEFT, RIGHT
5. Trimming: TRIM, LTRIM, RTRIM
6. Search and replace: POSITION, REPLACE
7. Padding: LPAD, RPAD
8. Pattern matching: LIKE, SIMILAR TO, regex
9. Sargability: function-on-column blocks indexes
10. Edge cases and pitfalls
11. Production reality
12. Practice ladder
13. Cross-links
14. Final takeaway

The string-function catalogue

The dozen or so functions that account for nearly every string operation you'll write:

Memorise this table; the rest of the chapter is the deeper notes for each.

Concatenation

A subtlety: || propagates NULL. 'hello' || NULL is NULL, not 'hello'. If country is NULL for a row, the entire bio for that row is NULL. Use COALESCE to substitute a default:

CONCAT (in dialects that have it) typically ignores NULL operands rather than poisoning the whole result — a different behaviour. Always check the docs for the dialect you're on before relying on either.

CONCAT_WS('separator', s1, s2, ...) ("with separator") is the portable way to join with a delimiter, skipping NULLs:

SELECT CONCAT_WS(', ', first_name, country, postcode) FROM customers;
-- Joins with ', '. NULLs are skipped — no double commas.

Case

LOWER(s) and UPPER(s) do exactly what they say. INITCAP(s) (Postgres) capitalises the first letter of each word.

Case is a frequent source of join misses (the chapter's hook bug). Two engineering rules:

1. For comparisons: lowercase both sides. WHERE LOWER(a) = LOWER(b) is the bread-and-butter case-insensitive join.
2. For storage: pick a normalised form (usually lowercase) and store it that way. Store email as lowercase on insert; comparisons are then exact-match. This is the production pattern — repair-on-read works in pinches but adds latency to every join.

Length and slicing

SUBSTRING(s FROM start FOR len) is the standard form; positions are 1-based. Postgres also accepts the function-call form SUBSTRING(s, start, len); SQLite uses SUBSTR(s, start, len). SQL Server uses SUBSTRING(s, start, len).

LEFT(s, n) and RIGHT(s, n) are convenience wrappers — the first / last N characters:

SELECT LEFT('Maria', 3);    -- 'Mar'
SELECT RIGHT('Maria', 3);   -- 'ria'

These two are the cleanest way to extract fixed-position prefixes and suffixes — like the year out of an ISO date string, or the file extension from a filename.

Trimming

TRIM(s) strips whitespace by default. TRIM('-' FROM s) strips any combination of the listed characters from both ends. LTRIM and RTRIM operate on one side only. Standard SQL: TRIM([LEADING|TRAILING|BOTH] [chars FROM] s).

In production data hygiene, TRIM is the single most-applied function. Any column that comes from human input — name, email, address line, search query — should be TRIMmed on insert (or the application should .trim() before sending). The reverse — leaving whitespace and TRIMming on read — works but pays the cost on every read. Better to normalise once.

Search and replace

REPLACE(s, from, to) substitutes every occurrence of from with to. Useful for normalising punctuation, scrubbing PII, removing characters:

Two replacements over the same input. REPLACE is global — it replaces every occurrence, not just the first.

POSITION(sub IN s) (Postgres) or INSTR(s, sub) (SQLite/MySQL) returns the 1-based position of the first occurrence, or 0 if not found:

SELECT POSITION('@' IN '[email protected]');   -- 6 (Postgres)
SELECT INSTR('[email protected]', '@');        -- 6 (SQLite/MySQL)

A common pattern: extract the domain from an email by combining SUBSTRING and POSITION:

SELECT SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM users;

For more complex extraction (@.+ to extract everything after the @), regex is the right tool — covered below.

Padding

LPAD(s, len, pad) left-pads s with the pad string until total length is len. RPAD is the right-pad version.

The classic use is zero-padding identifiers — turning 42 into 00042 for sorting or display alongside 00045. Sortable date strings like '2026-04-03' rely on this implicitly: each component is fixed-width.

Pattern matching

The simplest tier is LIKE, covered briefly in Filtering. % matches zero or more characters, _ matches exactly one.

SIMILAR TO (standard SQL, Postgres-supported) bridges between LIKE and full regex — supports |, *, +, ?, [] etc. Less commonly used than either LIKE or ~ (full regex) in production.

For real text matching, drop into the engine's regex:

-- Postgres: ~ is case-sensitive regex; ~* is case-insensitive.
WHERE email ~ '^[a-z0-9.]+@example\.com$';
 
-- SQLite: REGEXP operator (depends on extension being loaded).
WHERE email REGEXP '^[a-z0-9.]+@example\.com$';
 
-- MySQL: REGEXP or RLIKE.
WHERE email REGEXP '^[a-z0-9.]+@example\.com$';
 
-- SQL Server: no built-in regex; use LIKE patterns or extend with a CLR function.

Regex in SQL is the same regex you know — but it's expensive. LIKE 'foo%' can use a B-tree index; ~ '^foo' might (depends on planner). LIKE '%foo' and most non-anchored regex always require a full table scan. We'll cover this in Indexes and Performance.

Sargability

A predicate is sargable ("Search-ARGument-able") if the planner can use an index to evaluate it. Function-on-column predicates are usually not sargable — wrapping a column in a function hides it from the index.

-- ❌ Not sargable. Even with an index on email, this scans every row to apply LOWER.
WHERE LOWER(email) = '[email protected]';
 
-- ✅ Sargable. Direct comparison can use an email index.
WHERE email = '[email protected]';

Two production fixes:

1. Normalise on insert, so the column is already lowercase. Then your read query is the sargable form.
2. Build an expression index on LOWER(email). Then WHERE LOWER(email) = ... becomes sargable against that specific index. Postgres supports this; some other engines do too.

-- Postgres expression index: indexes the lowercased value.
CREATE INDEX users_email_lower ON users (LOWER(email));

The lesson: every per-row function in a WHERE is a candidate sargability bug. Audit hot-path queries for these. The full discussion is in Indexes and Performance; flagging them now so you start spotting them.

Edge cases and pitfalls

Empty string vs NULL

In standard SQL (and Postgres), '' (empty string) is not the same as NULL. LENGTH('') is 0; LENGTH(NULL) is NULL. Some Oracle versions historically treated '' as NULL — caused decades of porting headaches. Don't conflate the two.

Unicode normalisation

'café' and 'café' look identical but can be different strings — one is the precomposed é (U+00E9), the other is e + combining acute accent (U+0301). They're equal in visual terms but not equal in =. Postgres has unaccent() and ICU collations; rare but production-relevant for international apps.

Trailing whitespace from CHAR(n)

CHAR(n) is a fixed-length string type — values are always padded to length n with spaces. 'hi' stored in CHAR(5) becomes 'hi '. This bites in joins:

SELECT * FROM a JOIN b ON a.code = b.code;   -- might miss matches if one side is CHAR

Use VARCHAR(n) or TEXT instead; they don't pad. (Covered in the Schema and Constraints module.) If you're stuck with CHAR, use RTRIM on both sides.

Concatenating with NULL

SELECT 'hello, ' || NULL || '!';   -- NULL, not 'hello, !'

|| propagates NULL. CONCAT_WS(...) and CONCAT(...) (in MySQL/Postgres) typically skip NULLs. Always reach for COALESCE(col, '') if you want NULL to become empty.

Locale-dependent uppercase

UPPER('ß') is 'SS' in German locale, 'ß' in C locale. UPPER('i') in Turkish is 'İ', not 'I'. Subtle, rarely-hit, but enough to miss a join when a Turkish user types Istanbul. For deterministic comparisons, use COLLATE "C" or store normalised values.

Regex anchors

In Postgres ~, ^ matches start of string and $ matches end. Some older MySQL versions had subtly different anchor behaviour. Check the docs.

Production reality

The chapter's hook is the canonical production string-fix pattern: normalise both sides before joining. Either at write time (preferred) or at read time. In codefolio's stack, the customers.first_name column is human input — a future feature that joins against an external CRM by name would benefit from this defensive shape:

SELECT c.first_name, crm.profile
FROM customers c
JOIN external_crm crm
  ON LOWER(TRIM(c.first_name)) = LOWER(TRIM(crm.given_name));

A second pattern — extracting structured data from text columns:

-- Pull the domain part out of an email column for per-domain analytics.
SELECT SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain,
       COUNT(*) AS users
FROM users
GROUP BY domain
ORDER BY users DESC;

A third — scrubbing user input on write (this happens in application code, but the SQL primitives are what you'd use in a backfill migration):

-- One-time data fix: normalise existing email rows.
UPDATE users SET email = LOWER(TRIM(email)) WHERE email <> LOWER(TRIM(email));

The WHERE email <> LOWER(TRIM(email)) filter is the trick: only update the rows that need fixing. Without it, you'd update every row, and a VACUUM storm would follow.

Practice ladder

1. Lowercase every customer's name. Then trim leading/trailing whitespace from a hypothetical email column. Hint: LOWER, TRIM.
2. Extract the country domain (e.g., '.de', '.com') from an email column. Hint: SUBSTRING(email FROM POSITION('.' IN email)). (Or RIGHT(email, 3) if you trust the format.)
3. Find customers whose first name has at least 5 characters. Hint: LENGTH(first_name) >= 5.
4. Replace all spaces in a column with underscores. Hint: REPLACE(col, ' ', '_').
5. Why is WHERE LOWER(email) = '[email protected]' slower than the same query with the email column already stored lowercase? Hint: sargability. Where can the index help, and where can't it?
6. Write a query that returns each customer's first name padded to 10 characters with dots on the right. Hint: RPAD(first_name, 10, '.').
7. Predict the output of:
   SELECT 'hello' || NULL || '!' AS x;

   Hint: || propagates NULL.

Cross-links

• Previous module: Aggregation — once you have rows, summarise them. Now: per-row enrichment.
• Next in this module: Numbers — the numeric counterpart to this chapter.
• Forward reference: Indexes and Performance — sargability, expression indexes, the LIKE-with-trigram pattern for unanchored search.
• Forward reference: NULL and Three-Valued Logic — the full treatment of why || propagates NULL and how COALESCE + NULLIF repair it.

Final Takeaway

Your Turn

Before you move on, check your understanding with the coach — explain the idea, apply it, weigh the trade-offs, then defend your reasoning.