More SQL Please: Functions

Most relational database engines support the ability to create and call functions as part of a SQL statement. Most also have commonly used utility functions already built-in.

Functions that accept zero or more input values and produce a single output value per row are called "simple" or "scalar" functions. There are other types of functions that work with more than one row at a time and they are generally grouped into Aggregate Functions and Window Functions.

datetime() is a scalar function in SQLite that returns the current date and time.

SQLite and most other database engines will publish documentation on built-in functions available to use.

SELECT datetime();

| datetime() | |---------------------| | 2025-03-17 19:17:35 |

Functions can operate on row results and be combined and nested as long as the data types of the inputs and outputs are all compatible.

SELECT concat(
    upper(name_last), ', ', substr(name_first, 1, 1)
) 
AS name_full
FROM users
ORDER BY name_full ASC;

| name_full | |------------| | AMINI, P | | AMINI, Z | | CHEN, | | CRAWFORD, | | MORENO, E |

Functions can be called in almost any part of a SQL statement.

SELECT upper(u.name_first) AS first_name,
    strftime('%H:%M', t.date) AS transaction_time,
    abs(round(t.amount * 1.10, 2)) AS amount_incl_fee
FROM users u
INNER JOIN transactions t
    ON u.id = t.user_id
WHERE abs(amount_incl_fee) >= 20.0
ORDER BY strftime('%H', t.date) DESC;

| first_name | transaction_time | amount_incl_fee | |------------|------------------|-----------------| | ZARA | 19:51 | 44 | | ELENA | 14:09 | 82.5 | | NULL | 09:10 | 22 |

It is important to consider NULL input values in functions and understand how they are interpreted. For example the length() function in SQLite returns NULL, not 0.

SELECT name_first, length(name_first)
FROM users;

| name_first | length(name_first) | |------------|--------------------| | NULL | NULL | | Elena | 5 | | NULL | NULL | | Priya | 5 | | Zara | 4 |

coalesce() is a commonly used function to handle NULL values by returning a "default" value if the input you supply evaluates to NULL.

SELECT name_first, coalesce(length(name_first), 0)
    AS length
FROM users;

| name_first | length | |------------|--------| | NULL | 0 | | Elena | 5 | | NULL | 0 | | Priya | 5 | | Zara | 4 |

Some functions are capable of accepting a variable amount of arguments. These are called variadic functions.

A common example are string formatting functions like printf() (or format()). They often take in a "template" string that describes how you want the output string to be formatted, then any number of arguments that supply the data to fill it in with.

In this case, '%s' means we want a string type to appear in that spot, and '%.2f' means we want a floating-point number rounded to two deicmal places to appear in that spot.

A fun fact is that this is such a common problem to solve that most programming languages have printf() or some similar form of it.

SELECT printf('%s %s withdrew %.2f.',
    u.name_first,
    u.name_last,
    abs(t.amount)
) AS output
FROM users u
INNER JOIN transactions t
ON u.id = t.user_id
WHERE t.amount < 0;

| output | |----------------------------| | Zara Amini withdrew 40.00. |

Some database engines support creating custom functions using the CREATE FUNCTION) statement. Others, like SQLite require you to define custom functions outside of the database and load them separately which we will not cover here.

This example gives an idea of what a user-defined/custom function would typically look like in PostgreSQL, a different database engine, so ignore the syntax differences. This function could then be used anywhere a built-in function can be used by calling format_transaction(...).

CREATE FUNCTION format_transaction(
    name_first TEXT,
    name_last  TEXT,
    amount     NUMERIC
)
RETURNS TEXT
AS $$
BEGIN
    RETURN format('%s %s withdrew %.2f.',
        name_first,
        name_last,
        amount
    );
END;
$$;