Working with text and numbers

Functions work row by row

A function in the SELECT list runs once per row and its result takes the column's place: SELECT upper(title) FROM books; returns every title in capitals without changing what is stored. lower, length, trim and substr work the same way.

The result of a function has no name worth keeping, so this is where aliases earn their place: SELECT upper(title) AS heading FROM books;. Everything else about the query — filtering, sorting, grouping — is unchanged.

Joining text

Two bars side by side — || — join text into one value: SELECT author || ' wrote ' || title AS line FROM books;. Mixed types are converted on the way, so a year joins into the sentence as text without ceremony.

Functions and joins compose: upper(substr(title, 1, 3)) takes the first three characters and capitalises them. Reading such a line from the inside out is the same skill as reading a nested function call in any language.

Numbers, and the sharp edge in division

ROUND(value, 2) rounds to two decimals, and ABS removes the sign. Both are most useful around a calculation: ROUND(year / 10.0, 1) gives a decade with a fraction where a plain integer division would have thrown the remainder away.

That is the sharp edge worth knowing about: in SQLite, dividing two integers keeps an integer — 7 / 2 is 3, not 3.5. Write one side as a decimal (7 / 2.0) when the fraction is what you are after.

Text functions, one row at a time

length(text) counts characters, not bytes, so an accented letter is one. substr(text, start, length) counts from 1, and a negative start counts back from the end: substr('Dune', 2, 2) is 'un', and substr('Dune', -2) is 'ne'.

replace, trim, ltrim, rtrim and instr are the rest of the everyday set. instr(haystack, needle) returns the 1-based position or 0 when it is absent, which makes it the tool for splitting a value at a delimiter.

These functions run on the way out. WHERE upper(author) = 'DUNE' compares the shaped value and leaves the stored one alone, which is also why a function in a condition can stop an index from being used: the database has to compute it for every row first.

Numbers have storage classes

SQLite stores a number as an integer or a real, and typeof() tells you which. An integer stays exact; a real is a floating-point value, so 0.1 + 0.2 is not exactly 0.3. ROUND hides the difference in the display without changing the underlying value.

Integer division is the sharpest edge: 7 / 2 is 3, because both sides are integers. 7 / 2.0, CAST(7 AS REAL) / 2 and 7 * 1.0 / 2 all produce 3.5; the first is the shortest. CAST(value AS INTEGER) and CAST(value AS REAL) make the type explicit, which is easier to trust than relying on SQLite's conversion rules.

ROUND(value, digits) returns a real, not a formatted string. Formatting — two decimal places, a currency symbol — belongs to the program that displays the answer, not to the query that computes it.

Numbers that are not numbers

SQLite will store the text '12' in an INTEGER column and convert it on the way in, but a value that is not a number at all — 'twelve' — stays text, and arithmetic on it treats it as zero. typeof() is the honest check, and CAST is the explicit conversion. A stricter database would have refused the insert; here it succeeds, and the mistake surfaces later as a total that is quietly too small.

The fix is not to cast everything in sight. Cast where a value enters the table, so the column holds one kind of thing, and let the query trust it afterwards. A cast inside a comparison is usually a sign that the data already drifted.

Shaping the answer as it comes back

SELECT upper(title) AS heading, length(title) AS letters
FROM books
ORDER BY letters DESC;

SELECT author || ' — ' || title AS line
FROM books;

SELECT title, ROUND(year / 10.0, 0) AS decade
FROM books;

Try these next

• Build a surname, initial label from a full name with instr and substr.
• Compute a percentage with ROUND(part * 100.0 / total, 1) and check the type with typeof.
• Replace every double space in a title with one, then trim the result.