We closed module 5 with a list of gaps: you're still returning name and last_name in two columns when you want one, you still can't put a piece of text in upper case, extract the size from a product name or pull the domain out of an email address. This module starts there, because text is what gets manipulated most when presenting results and what diverges most between engines. But before the first function there's a confusion to clear up that a lot of people carry around for years: the difference between a scalar function and an aggregate function. You saw the aggregate ones in 04-04; this module's belong to the other family. If that distinction is clear to you, the rest is vocabulary.

1. Scalar against aggregate: one row in, one row out
2. Measuring and changing case: LENGTH, UPPER, LOWER, INITCAP
3. Cleaning and padding: TRIM, LPAD, RPAD
4. Extracting, locating and splitting
5. Composing text: CONCAT_WS, FORMAT and 02-02's ||
6. Extraction with regular expressions
7. Real GreenStore cases
8. Functions over columns and indexes
9. Comparison table by engine
10. Common Mistakes and Tips
11. Exercises
12. Conclusion

A scalar function takes one or more values from the same row and returns one value for that row. If the query reads 4 rows, the result has 4 rows.

An aggregate function takes a set of rows and returns a single value:

A single row. And look at MAX(LENGTH(name)): it combines the two families. First LENGTH is applied to each row (scalar), then MAX collapses the four results into one (aggregate). The order is never the other way round.

The rule in one sentence: a scalar function transforms; an aggregate one summarises. UPPER(name) transforms each name; COUNT(name) summarises them all into one number. Every function in module 6 is scalar.

That Castellón —9 characters, 10 bytes— sums up an inexhaustible source of errors: in UTF-8, an accented character takes two bytes.

The third name carries no accented letters and the two numbers match; the first two each carry two, and the byte count runs two ahead. When VARCHAR(n) limits characters (PostgreSQL) none of this matters; when it limits bytes (MySQL with certain types, Oracle with VARCHAR2(n BYTE)) it's the classic cause of value too long with accented names.

Three warnings about INITCAP: it capitalises every word, short prepositions and units included (Loofah Scrubber (Pack Of 3), Extra Virgin Olive Oil 500 Ml), which looks wrong on a commercial title and is perfect for normalising city names; it respects accents (INITCAP('CASTELLÓN') → Castellón); and it doesn't exist in MySQL, SQLite or SQL Server, making it the function most missed when porting code from PostgreSQL or Oracle.

UPPER and LOWER handle accents properly because they depend on the database's collation, the same en-US-x-icu you configured for ORDER BY in 02-05.

TRIM(BOTH 'x' FROM s) is standard SQL syntax, not an ordinary call, and that's why it carries keywords instead of commas. It's used when cleaning up imports: leading zeros on a code, stray quotes from a CSV, trailing full stops.

Two traps. TRIM(BOTH 'ab' FROM s) doesn't remove the string 'ab': it removes any of the characters a or b. And LPAD guarantees an exact length: if the string is longer it trims it (LPAD('Valencia', 5, '.') → 'Valen').

Three things to commit to memory: positions start at 1, not at 0 (if you're coming from a programming language, that's the first week's mistake); POSITION and STRPOS return 0 when they don't find anything, not NULL, so you can compare them without dragging in 04-03's three-valued logic; and SPLIT_PART returns the empty string if piece n doesn't exist, again not NULL.

SPLIT_PART is one of PostgreSQL's most useful functions and it has no direct equivalent in most engines. What with STRPOS + SUBSTRING + an easily forgotten - 1 costs two nested expressions, here is a single call:

In 02-02 you used || to join name and last_name, and in 04-03 you discovered its trap: if one operand is NULL, the whole result is NULL. Here are the alternatives.

CONCAT_WS (With Separator) doesn't just ignore nulls: it also leaves the separator out where there's no value. Let's pick up 04-03's reflexive LEFT JOIN:

Look at rows 1 and 4: referrer_pipe is *(null)* and referrer_ws is the empty string.

The honest conclusion: CONCAT_WS solves the case "one of the fields is missing". It doesn't solve "there's nobody there", which needs explicit text like 'Direct signup'. For that you need COALESCE, in 06-04.

FORMAT builds templates printf-style:

Advantages over ||: it converts the types by itself (no ::TEXT needed), the template can be read at a glance and %s with a NULL argument produces the empty string instead of nulling everything out. The placeholders %I (identifier) and %L (quoted literal) are for generating safe dynamic SQL and they'll show up in module 10.

In 04-01 you used LIKE, ILIKE and ~ to decide whether a string matches a pattern. Here regular expressions do something else: they extract and replace.

The practical case: pulling the size out of the product name. GreenStore's names end with a format (500 ml, 30 g, 1 kg, 20 bags) except four that don't carry one — the same four you found in 04-01.

The four products with no size —11, 12, 13 and 18— return NULL from SUBSTRING and the untouched name from REGEXP_REPLACE: no match, no replacement.

A detail about the alternation (ml|kg|g|L|units|bags): kg comes before g. The other way round, an unanchored engine would match the g of kg and the result would be wrong. The alternation tries the options in the order written, so the most specific goes first.

REGEXP_MATCHES's trap: it returns a set of rows, not a value; put in the SELECT, the rows with no match disappear (the 20 products would come out as 16). For one value per row use SUBSTRING(… FROM pattern) or, from PostgreSQL 15 onwards, REGEXP_SUBSTR(s, pattern).

SPLIT_PART(last_name, ' ', 1) takes advantage of the fact that the two surnames sit together separated by a space: it's the "backfill that splits a column" announced in 05-06.

Section 1's two families coexist here: SPLIT_PART is scalar and gets computed row by row; COUNT(*) summarises each group. And the GROUP BY repeats the whole expression, not the alias: it's the "grouping by an expression" 04-05 flagged.

GreenStore's data is clean, but a real import file never is. The canonical recipe is a chain of three functions, in this order:

INITCAP(LOWER(TRIM(x))) turns ' vaLENcia ', 'VALENCIA' and 'valencia' into the same 'Valencia': TRIM removes the edges, LOWER levels the case and INITCAP rebuilds it. It's the pattern you'll use whenever you group by a text column that was typed in by hand.

Important warning: this is presentation masking, not anonymisation. The original value is still intact in the table and anybody with access can see it. Real anonymisation —pseudonymisation, aggregation with a minimum threshold, deletion— is a legal (GDPR) and architectural matter, not a string-function one. Use it so a report doesn't display full email addresses, never as a substitute for a data protection policy.

A warning for the future. A filter like WHERE LOWER(email) = 'lucia.martinez@example.com' returns the right row, but by wrapping the column in a function the engine stops comparing the column's values and starts comparing computed values, which no ordinary index contains. The solution exists —the expression index, CREATE INDEX … ON customers (LOWER(email))— but indexes are module 8 and there you'll see it measured with EXPLAIN in 08-03. For now, hold on to the rule: a function over a column in the WHERE has a cost. In the SELECT there's no problem: transforming what you've already read is free compared with reading it.

Two portability traps that cost whole afternoons. In Oracle, the empty string '' is NULL: LENGTH('') returns NULL, not 0, and everything you learned in 04-03 about telling '' apart from NULL doesn't apply there. And in SQL Server, LEN ignores trailing spaces: LEN('Valencia ') is 8, not 10; to count properly you need DATALENGTH.

• Confusing scalar with aggregate. LENGTH(name) gives one row per product; MAX(LENGTH(name)) gives a single one. If the result has fewer rows than you expected, check whether you've slipped an aggregate in by accident.
• Counting from 0. In SQL, string positions start at 1. And LPAD guarantees an exact length, not a minimum one: if the string is longer, it trims it.
• Using || with nullable columns. A single NULL nulls out the whole expression. CONCAT_WS to join fields, COALESCE (06-04) to put in default text.
• Believing CONCAT_WS solves every null. With all the arguments null it returns '', which in a report reads as an empty cell. And REGEXP_MATCHES in the SELECT makes the rows with no match disappear: use SUBSTRING(… FROM pattern).
• Getting a regex alternation's order wrong. (g|kg) matches the g of kg. The most specific goes first.
• Confusing TRIM(BOTH 'ab' FROM s) with removing the string 'ab'. It removes the individual characters a and b. And careful: in PostgreSQL LENGTH counts characters, in MySQL it counts bytes.
• Tip: always normalise with INITCAP(LOWER(TRIM(x))), in that order, before grouping by hand-typed text.
• Tip: prefer SPLIT_PART to STRPOS + SUBSTRING when the separator is fixed: it reads better and it doesn't have the - 1 everybody forgets. And use FORMAT for templates with more than two pieces.

Marketing wants labels for the catalogue. For each active product in categories 1 and 4, return the id formatted as GS-00001 (a prefix, a hyphen and five digits with leading zeros), the name without its size and in title format, the size separately (or NULL if it doesn't carry one) and the length in characters of the original name. Sort by id.

Customer support needs a masked contact view. For each customer, return the full name in one column, the initials of the first name and of both surnames (L.M.S. for Lucía Martínez Soler) and the email with the username hidden except for its first two letters. Add the top-level domain (com, pt, fr) and, in a second query, count how many customers there are of each. (Hint: the surnames are separated by a space; SPLIT_PART and LEFT are enough.)

A colleague has written LEFT(last_name, POSITION(' ' IN last_name)) AS first_surname over customers. (1) What two problems does the result have? Look at customers 9 and 10. (2) Fix it using POSITION. (3) Fix it using SPLIT_PART and explain why that version has neither of the two problems.

Solution 1

9 products: 5 from Food and 4 from Drinks. The ::TEXT is compulsory because LPAD expects text and id is an integer (conversions: 06-04). And there you can see section 2's INITCAP defect: Cold-Pressed Orange Juice, where the part after the hyphen has been capitalised because INITCAP treats the hyphen as a word boundary. Fixing it requires CASE (06-05) or a targeted REPLACE.

Solution 2

The two French customers have a single surname, and SPLIT_PART(last_name, ' ', 2) returns the empty string: hence the C.D.. with two full stops in a row. SPLIT_PART doesn't fail and doesn't return NULL, it returns '', and the || concatenates it without complaining. Fixing it means asking "is there a second surname?", that is, NULLIF (06-04) or CASE (06-05).

The count —GROUP BY SPLIT_PART(email, '.', 3) with COUNT(*)— gives 11 com customers, 2 fr and 2 pt, the same 15 split the same way as in section 7.2.

Solution 3

1. Run over customers 1, 9 and 10 it gives Martínez (with a trailing space) for the first and the empty string for the other two.

• Problem A: POSITION returns the position of the space, so LEFT takes it too. There's a - 1 missing.
• Problem B: when there's no space, POSITION returns 0 and LEFT(s, 0) is the empty string: customers 9 and 10, with a single surname, lose the surname entirely. With the - 1 it would be even worse, because LEFT(s, -1) returns everything except the last character: Duboi.

2 and 3. With POSITION you have to guarantee there's always a space; with SPLIT_PART you need nothing at all:

Both return the same thing:

The second has neither of the two problems because SPLIT_PART doesn't work with positions: it splits by the separator and returns the piece you asked for. If there's no separator, piece 1 is the whole string. No - 1 to forget, no special case to handle. That's the moral: when a function exists that expresses your intent, use it instead of rebuilding it out of position arithmetic.

You now know how to transform text:

• You can tell a scalar function from an aggregate one: the first transforms row by row, the second summarises many rows into one. The whole of module 6 is scalar.
• You measure with LENGTH (characters) and OCTET_LENGTH (bytes), you change case with UPPER, LOWER and INITCAP and you normalise with INITCAP(LOWER(TRIM(x))).
• You clean with TRIM and its variants, you pad with LPAD/RPAD (which also trim), you extract with SUBSTRING/LEFT/RIGHT remembering that positions start at 1, you locate with POSITION/STRPOS (which return 0 when they find nothing) and you split with SPLIT_PART, the function that removes all the position arithmetic.
• You compose with CONCAT_WS, which solves 02-02's NULL trap —except when every argument is null, a case that waits for COALESCE in 06-04— and with FORMAT for templates.
• You extract and replace with REGEXP_REPLACE and SUBSTRING(… FROM pattern), different from 04-01's LIKE and ~, which only decided whether something matched. And you know that a function over a column in the WHERE has a cost that gets measured in module 8.

In the next lesson, numeric functions, the same idea applied to numbers: ROUND with its siblings TRUNC, CEIL and FLOOR, arithmetic with MOD, POWER and ABS, and two traps that cost real money. The first: 10 / 3 isn't 3.33, it's 3, and there are three different ways of fixing it. The second, more serious: ROUND(2.5) and ROUND(2.5::DOUBLE PRECISION) don't return the same thing in PostgreSQL, and that one-cent difference, multiplied by a million invoice lines, is the reason 01-04 said money is never stored in floating point. Let's prove it.