SQLite format() Function: Version and Examples

SQLite format() returns a string created from a printf-style format string and its arguments. SQLite introduced this name in version 3.38.0 by renaming the SQL printf() function; printf() remains an alias for compatibility. On versions older than 3.38.0, use printf() instead. See the SQLite 3.38.0 release notes and built-in scalar functions reference.

Syntax

format(FORMAT, ...)

The format string uses % substitutions, with values supplied by the following arguments. The formatting rules are the same as SQLite printf(); that page covers width, precision, and SQLite-specific quote specifiers.

Examples

Insert text into a format string:

SELECT format('Hello, %s!', 'SQLite');

Hello, SQLite!

Pad an integer with zeroes:

SELECT format('%04d', 17);

0017

Format a decimal number to two places:

SELECT format('%.2f', 123.456);

123.46

Format NULL arguments

SQLite’s %s, %q, and %Q format specifiers produce different results for a SQL NULL. In the SQLiz playground engine (SQLite 3.49.1), this query returns an empty string, (NULL), and the text NULL:

SELECT format('%s', NULL) AS string_value,
       format('%q', NULL) AS quoted_text,
       format('%Q', NULL) AS sql_literal;

string_value | quoted_text | sql_literal
-------------+-------------+-----------
             | (NULL)      | NULL

%Q returns text spelling the SQL NULL literal, not a SQL NULL value. These results are verified with the playground’s SQLite version; check your application’s build when exact NULL formatting matters. For examples you can run in the browser, open the SQLite SQL playground.

Use format() on SQLite 3.38.0 or later. Use printf() when your database must also run on older SQLite versions; both names are available in current releases.