Functions, Dates and CASE

Introduction

SQL can do more than fetch columns as they are. Built-in functions transform values as the rows come out: tidy up text, round numbers, pull apart dates, or label rows with if-then logic.

Text functions

• UPPER and LOWER change case, LENGTH counts characters.
• SUBSTR(text, start, length) takes part of a text. The first character is position 1.
• REPLACE(text, find, with) swaps pieces of text. TRIM removes spaces at both ends.

Join text together with ||:

Numbers

Beware of integer division. When both numbers are whole, SQLite throws away the remainder:

Dates

SQLite stores dates as text in the form YYYY-MM-DD, which sorts correctly. Date functions pick them apart or move them.

strftime formats a date: %Y is the year, %m the month, %d the day. This groups orders by month:

To count days between two dates use julianday:

COALESCE: a fallback for NULL

COALESCE(a, b, ...) returns the first value that is not NULL.

CASE: if-then in a query

CASE returns different values depending on conditions, like an if/else.

The first WHEN that is true wins, and ELSE is the fallback. CASE also works inside aggregates, to count different things in one pass:

Try it yourself

Show each product's name with a column stock_level that says low when stock is under 50, ok when it is under 150, and plenty otherwise.

Summary

• Text functions: UPPER, LOWER, LENGTH, SUBSTR, REPLACE, TRIM, and || to join.
• Number functions: ROUND, ABS, CAST. Watch out for integer division.
• Dates are text. Use strftime, date and julianday to work with them.
• COALESCE replaces NULL, and CASE WHEN ... THEN ... ELSE ... END adds logic.