Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 33 ยท Querying

Built-in Functions

A guided tour of SQLite's built-in functions: string, math, NULL-handling and aggregate functions, plus printf, group_concat, iif, coalesce, instr and substr.

SQLite's function library is small compared with PostgreSQL or Oracle, but it covers what most applications need: string handling, a few math helpers, NULL tools, and the usual aggregates. Knowing the exact names, and the SQLite-specific ones such as printf(), instr() and group_concat(), saves you from writing application code for work the database can do in one expression.

This lesson walks through the functions by family. Everything runs live; the sample outputs below come from the real engine on this page. One caution first: SQLite can be compiled with different options, so some functions (certain math functions in particular) exist in the official sqlite3 shell but not in every build. The lesson marks those.

Three kinds of function

Scalar One value in, one value out. length(), upper().
Aggregate Many rows in, one value out. sum(), count().
Window Aggregates that keep every row. Covered later.

Scalar functions work on each row separately.

1 / 2

String functions

Two facts about SQLite strings shape everything else: positions are 1-based, and length() counts characters for text (but bytes for blobs).

Function What it does
length(x) Characters in text (bytes in a blob)
upper(x), lower(x) Change case (ASCII letters only)
trim(x), ltrim(x), rtrim(x) Remove spaces, or any characters listed in a second argument
substr(x, start, len) Slice; a negative start counts from the end
instr(x, y) Position of the first y in x, or 0 if absent
replace(x, find, new) Replace every occurrence
concat(a, b, ...) Join values, treating NULL as empty text
concat_ws(sep, a, b, ...) Join with a separator, skipping NULL values
hex(x), char(n, ...), unicode(c) Convert between text, hex and code points
quote(x) The value as an SQL literal, with quotes escaped
SQL Tour of the string functions

Press Run (or Ctrl + Enter) to see the result here.

In the first result chars is 5, first_six is SQLite, last_five is rocks and found_at is 8. In the second, the || operator turns the whole expression into NULL when any side is NULL, while concat() returns ab and concat_ws() returns a-b. quote() returns 'it''s', which is handy when you build SQL for debugging (never for real queries; see the lesson on parameters).

A classic use of substr() and instr() together is extracting the text before a delimiter:

SQL First word of every title ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQLite has no built-in split(). For titles without a space, instr() returns 0 and the expression would return an empty string, so add CASE WHEN instr(...) > 0 when your data may lack the delimiter.

Numeric functions

The always-available numeric helpers are abs(), round(), sign() and the scalar form of max() and min() (with two or more arguments they compare values, with one argument they are aggregates). round(x, n) rounds half away from zero, so round(2.5) is 3 and round(-2.5) is -3. The % operator gives the remainder.

If the library is compiled with math functions (the official sqlite3 shell does this), you also get ceil(), floor(), sqrt(), power(), exp(), ln(), log10(), log2(), mod(), trunc() and the trigonometry functions. The sql.js engine on this site has only some of them, and its log() means natural logarithm, so do not copy math-heavy examples between environments without testing.

SQL Numbers, rounding and NULL tools

Press Run (or Ctrl + Enter) to see the result here.

The NULL tools deserve a closer look:

  • coalesce(a, b, c, ...) returns the first argument that is not NULL. Use it for defaults: coalesce(nickname, name).
  • ifnull(a, b) is the two-argument version.
  • nullif(a, b) returns NULL when the two values are equal, otherwise a. It is the standard way to avoid division by zero: total / nullif(count, 0).
  • iif(test, then, else) is a short CASE expression (SQLite 3.32+). Use it for simple two-way choices; use CASE for more branches.

Two more functions you will meet: random() returns a random 64-bit integer, and randomblob(n) returns n random bytes (lower(hex(randomblob(8))) makes a simple random identifier). likely(x) and unlikely(x) are hints to the query planner that return their argument unchanged; you will rarely need them.

printf() and format()

printf(format, ...) (also named format()) builds text from values using C-style placeholders. It is excellent for reports: padding, decimals, thousands separators and hex output all happen in the query.

SQL Formatting with printf

Press Run (or Ctrl + Enter) to see the result here.

%d is an integer, %f a decimal, %s text, %x hexadecimal, %% a literal percent sign, and %,d is a SQLite extension that adds thousands separators. A number between % and the letter sets the width, - left-aligns, 0 pads with zeros, and .2 sets the decimals. %q doubles single quotes (like quote() without the surrounding quotes) and %Q also adds the quotes.

Aggregate functions

Aggregates collapse many rows to one value. SQLite ships count(), sum(), total(), avg(), min(), max() and group_concat(); the newer string_agg() is an alias for group_concat() with a required separator.

A few details that matter:

  • count(*) counts rows; count(col) counts non-NULL values; count(DISTINCT col) counts distinct non-NULL values.
  • sum() of no rows (or only NULLs) is NULL, but total() always returns a floating-point number, so it gives 0.0 instead.
  • sum() of integers is an integer and raises an error on overflow; total() never overflows because it uses floating point.
  • group_concat(x, sep) joins values into one string, comma by default. Since SQLite 3.44 you can write ORDER BY inside the call to control the order, and DISTINCT is allowed when you use the one-argument form.
SQL Aggregates on the bookstore ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The first result shows 5 authors but only 4 birth years, because count(born) skips Daniel Brook's NULL. sum() over zero rows displays an empty (NULL) cell; total() shows 0. The FILTER (WHERE ...) clause (SQLite 3.30+) makes a single aggregate count only the rows that match, which replaces the older SUM(CASE WHEN ... THEN 1 ELSE 0 END) trick.

group_concat

group_concat() is the aggregate people love, because it turns a list of child rows into one readable cell:

SQL One row per author with a list of books ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Each author's titles appear on one line, in alphabetical order, separated by a semicolon. Without an ORDER BY inside the call the order within the group is not guaranteed, so add it whenever the order matters. For database versions older than 3.44, sort in a subquery before grouping.

The same function names appear in other databases with different spelling: PostgreSQL uses string_agg, MySQL uses GROUP_CONCAT, SQL Server STRING_AGG. For the portable version of these ideas, see aggregate functions in the SQL tutorial.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does instr('SQLite rocks', 'rock') return?

    Positions start at 1, and "rock" begins at the 8th character. instr() returns 0 only when nothing is found.
  2. 2. sum(x) over a query that returns no rows gives NULL. Which function gives 0.0 instead?

    total() always returns a floating-point number, so an empty set gives 0.0 instead of NULL.
  3. 3. What do 'a' || NULL || 'b' and concat('a', NULL, 'b') return?

    The || operator propagates NULL, while concat() treats NULL as an empty string.
  4. 4. Which expression avoids a division-by-zero error and returns NULL when n is 0?

    nullif(n, 0) turns a zero divisor into NULL, and dividing by NULL gives NULL. In SQLite dividing by zero already returns NULL, but nullif() makes the intent explicit and stays portable.
  5. 5. How do you control the order of items inside group_concat() in SQLite 3.44 and later?

    SQLite supports aggregate ORDER BY inside the call, for example group_concat(title, '; ' ORDER BY title).

Summary

  • String positions are 1-based; substr() and instr() together extract text around a delimiter.
  • || yields NULL if any side is NULL; concat() and concat_ws() skip NULL.
  • coalesce(), ifnull(), nullif() and iif() handle missing values and inline decisions.
  • printf() formats text, padding and numbers (including %,d for thousands).
  • count(col) skips NULL; sum() of nothing is NULL, total() is 0.0.
  • group_concat(x, sep ORDER BY ...) builds lists; FILTER (WHERE ...) makes a conditional aggregate.
  • Math functions depend on how SQLite was built; test before relying on them.

Next: Joins, Subqueries and CTEs, combining tables and building queries from smaller steps.