Five functions cover most text formatting in SQL: two for case, one for glueing, one for slicing and one for locating. None mutates stored data — a formatted column is a computed one, which is exactly why you can compose them freely without any anxiety about the source rows.
Which functions reshape the case of text?
UPPER and LOWER normalise display and matching. A genre tag on a badge wants shouting; a host slug wants whispering — both derived live from same stored values:
SELECT title, UPPER(genre) AS genre_tag, LOWER(host) AS host_low
FROM episodes ORDER BY id LIMIT 4;
title genre_tag host_low
----------------------------------- ----------- --------
Mapping the shore with LiDAR SCIENCE rui
The boundary of a map SCIENCE rui
Street photography at night PHOTOGRAPHY dev
Developing film in a hotel bathroom PHOTOGRAPHY dev
Folded case is also the cheap way to make case-insensitive filters portable — WHERE LOWER(email) = '[email protected]' behaves identically on strict and lenient engines, sidestepping the LIKE-vs-equals case mess from lesson 7. PostgreSQL and newer engines can both keep an index working for such a pattern only if one exists on the expression; ordinary column indexes do not serve expressions.
How do you glue columns into one label?
The double-pipe operator joins text values, including literals mixed in midstream. Note SQLite renders a NULL operand as NULL — an empty-looking gap in the billing line — so guard backfills with COALESCE when a column can be blank (lesson 15 owns that function):
SELECT host || ' hosts #' || id || ': ' || title AS billing
FROM episodes ORDER BY id LIMIT 4;
billing
-------------------------------------------------
Rui hosts #1: Mapping the shore with LiDAR
Rui hosts #2: The boundary of a map
Dev hosts #3: Street photography at night
Dev hosts #4: Developing film in a hotel bathroom
Numbers interleave seamlessly — SQLite and friends coerce them to text. The concatenation operator marks this lesson's dialect quirk zone: PostgreSQL, SQLite and Oracle speak it natively; SQL Server instead expects + or CONCAT(); MySQL defaults to reading || as boolean OR unless a server flag changes it, so CONCAT is the only spelling to trust there (including the CONCAT_WS joiner with a fixed separator).
What does SUBSTR slice, from either end?
SUBSTR(text, start, length) cuts with one-based indexing — start at 1, take 12 characters — and a negative start counts from the tail, which is a two-for-one trick for month keys and short labels:
SELECT title, SUBSTR(title, 1, 12) AS head12, SUBSTR(title, -8) AS tail8
FROM episodes ORDER BY id LIMIT 4;
title head12 tail8
----------------------------------- ------------ --------
Mapping the shore with LiDAR Mapping the th LiDAR
The boundary of a map The boundary of a map
Street photography at night Street photo at night
Developing film in a hotel bathroom Developing f bathroom
The same slice logic, taken to its most useful form, extracts the year-month key out of an ISO date — a derived column the sorting and grouping lessons will rely on heavily:
SELECT SUBSTR(released, 1, 7) AS month_key, title
FROM episodes ORDER BY released LIMIT 4;
month_key title
--------- -----------------------------------
2024-11 Reading an old survey
2025-01 Street photography at night
2025-01 Developing film in a hotel bathroom
2025-02 Composing with a single lens
Where does INSTR locate a piece of text?
INSTR(text, needle) reports the one-based position of the first occurrence, or 0 when absent — 0, note, not NULL, so the function both finds and certifies absence in one call. Watch a lower-case a walk through guests:
SELECT guest, INSTR(guest, 'a') AS first_a
FROM episodes ORDER BY id LIMIT 6;
guest first_a
------------- -------
Nadia Okoye 2
Tom Whitfield 0
Lena Marsh 4
Marcus Bell 2
Priya Raman 5
Tom has no lower-case a — 0 says so — and the blank row is a NULL guest passing through the expression unchanged; functions generally return NULL when fed NULL, a fact worth internalising before it surprises a report. Positions pair naturally with SUBSTR for scripted splits: locate a delimiter with INSTR, then slice around it — the opening move for the cleaning recipes of the next lesson.
| Function | Role | Same job, other dialects |
|---|---|---|
| UPPER / LOWER | case folding | identical spelling across the major engines |
| || | concatenation | SQL Server: + or CONCAT; MySQL: CONCAT (|| means OR there) |
| SUBSTR(s, i, n) | slice | SQL Server: SUBSTRING; PostgreSQL also accepts substring(s from i for n) |
| INSTR(s, t) | locate | SQL Server: CHARINDEX(t, s) — argument order differs; PG: POSITION(t IN s) |
The table is reference, not evidence — only the SQLite spellings were executed here, as elsewhere in this course. What transfers is the shape of every operation; the spellings are one documentation lookup away, and differ chiefly in argument order for the locate family.
Quick reference
- UPPER/LOWER normalise both display and case-sensitive matching.
- || concatenates; a NULL operand silently swallows the whole joined expression.
- SUBSTR is one-based, length-third, and negative indexes count from the end.
- INSTR answers 0 for absent text — absence is measurable, not untypeable.
- String expressions never modify stored rows; the table only borrows expectations.
Realistic drill: build a one-line episode summary reading 'SCIENCE — 47 min — Mapping the shore with LiDAR', derive each host's initials from their stored name, and write one query listing guests whose surname contains 'ma' — upper or lower, your choice, but explain in one sentence why you chose it.