SQL chapters

Chapter 9 of 17

Built-in scalar functions

Try this in our SQLite Compiler →

The idea

SQLite offers built-in functions such as upper(), length(), round() and coalesce(). A scalar function produces a value for each row; aggregates such as count() combine rows. SQLite does not support PostgreSQL's CREATE FUNCTION ... LANGUAGE SQL syntax. Custom functions require the host database API.

Worked example

SELECT upper('coding') AS label, length('coding') AS letters, round(3.14159, 2) AS rounded, coalesce(NULL, 'unknown') AS fallback;

What to check

The values are CODING, 6, 3.14 and unknown. coalesce chooses the first non-NULL argument, not the first nonempty string.

Practice task

Compare coalesce('', 'fallback') with coalesce(NULL, 'fallback'). Explain their different results.

Check your understanding

Explain the result in your own words before changing the example. Change one thing at a time, compare the result with your prediction, and keep the version that demonstrates the concept clearly.

Try it yourself

Output
Run your project to see output here.
Ctrl/Cmd+Enter runs · Ctrl/Cmd+S downloads the project · Local auto-save is always on