Five functions cover most of the text formatting in the SQL. There are two for the case, one for the glueing, one for the slicing and one for the locating. None of them mutates the 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.
The previous lesson in this module covered "How Do Expressions and CASE Transform Columns?", and the next lesson in this module is about "How Do You Clean Messy Text Data With SQL?".
Which functions reshape the case of the text?
The UPPER and the LOWER normalise the display and the matching. A genre tag on a badge wants the shouting, and a host slug wants the whispering. Both of them are derived live from the 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 devThe folded case is also the cheap way to make the case-insensitive filters portable. The filter WHERE LOWER(email) = '[email protected]' behaves identically on the strict engines and on the lenient ones, and it sidesteps the case mess between the LIKE and the equals that lesson 7 described.
PostgreSQL and the newer engines can both keep an index working for such a pattern only if an index exists on the expression itself. The ordinary column indexes do not serve the expressions.
How do you glue the columns into one label?
The double-pipe operator joins the text values, and it includes the literals that are mixed in midstream. Notice that SQLite renders a NULL operand as NULL. That is an empty-looking gap in the billing line, so you guard the backfills with the COALESCE when a column can be blank, and 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 bathroomThe numbers interleave seamlessly, because SQLite and its friends coerce them to the text.
The concatenation operator marks this lesson's dialect quirk zone. PostgreSQL, SQLite and Oracle speak it natively. The SQL Server instead expects the + or the CONCAT(). MySQL defaults to reading the || as the boolean OR unless a server flag changes it, so the CONCAT is the only spelling to trust there, including the CONCAT_WS joiner with the fixed separator.
What does the SUBSTR slice, from either end?
The SUBSTR(text, start, length) cuts with the one-based indexing. You start at 1 and you take 12 characters. A negative start counts from the tail, which is a two-for-one trick for the month keys and the 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 bathroomThe same slice logic, taken to its most useful form, extracts the year-month key out of an ISO date. That is a derived column which the sorting and the 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 lensWhere does the INSTR locate a piece of text?
The INSTR(text, needle) reports the one-based position of the first occurrence, or 0 when it is absent. Note that it is 0 and not NULL, so the function both finds the text and certifies its absence in one call.
Watch a lower-case a walk through the 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 5Tom has no lower-case a, and the 0 says so. The blank row is a NULL guest that is passing through the expression unchanged. The functions generally return a NULL when they are fed a NULL, and that is a fact worth internalising before it surprises a report.
The positions pair naturally with the SUBSTR for the scripted splits. You locate a delimiter with the INSTR, and then you slice around it. That is 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) |
That table is the reference and not the 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 they differ chiefly in the argument order for the locate family.
Quick reference
- The UPPER and the LOWER normalise both the display and the case-sensitive matching.
- The || concatenates, and a NULL operand silently swallows the whole joined expression.
- The SUBSTR is one-based, the length is the third argument, and the negative indexes count from the end.
- The INSTR answers 0 for the absent text, so the absence is measurable and not untypeable.
- The string expressions never modify the stored rows. The table only borrows the expectations.
Realistic drill: build a one-line episode summary that reads 'SCIENCE — 47 min — Mapping the shore with LiDAR'. Derive the initials of each host from their stored name, and write one query that lists the guests whose surname contains 'ma', in either the upper case or the lower case of your choice. Explain in one sentence why you chose it.