SQLite FORMAT() Function

The SQLite FORMAT() function works like the printf() function from the standard C library. The first argument is a format string that specifies how to construct the output string using values taken from subsequent arguments. If the format argument is missing or NULL then the result is NULL. The %n format is silently ignored and does not consume an argument. The %p format is an alias for %X. The %z format is interchangeable with %s. If there are too few arguments in the argument list, missing arguments are assumed to have a NULL value, which is translated into 0 or 0.0 for numeric formats or an empty string for %s.

Syntax

FORMAT(format, ...)

Parameters

Return Value

Returns the formatted string.

Example 1: format() example

The example below shows the usage of FORMAT() function.

SELECT FORMAT("Decimals: %d %i", 200, 300);
Result: Decimals: 200 300

SELECT FORMAT("More Decimals: %ld %li", 20000, 30000);
Result: More Decimals: 20000 30000

SELECT FORMAT("Octals: %o %#o", 100, 100);
Result: Octals: 144 0144

SELECT FORMAT("Hexadecimals: %x %#x %X %#X", 100, 100, 100, 100);
Result: Hexadecimals: 64 0x64 64 0X64

SELECT FORMAT("Strings: %s", "Hello");
Result: Strings: Hello

SELECT FORMAT("Scientific notation: %e %E", 123.45, 123.45);
Result: Scientific notation: 1.234500e+02 1.234500E+02

SELECT FORMAT("Floats: %2.0f %2.2f %2.4f", 3.1416, 3.1416, 3.1416);
Result: Floats:  3 3.14 3.1416

SELECT FORMAT("Positive signed number = %+.2f", 3.1416); 
Result: Positive signed number = +3.14

SELECT FORMAT("Padded number = %05d", 89);
Result: Padded number = 00089

SELECT FORMAT("Number with Width = %*d", 5, 89);
Result: Number with Width =    89

Example 2: Format date string

Consider a database table called BirthDay with the following records:

The statement given below can be used to get the full date using values of Day, Month and Year columns.

SELECT *, FORMAT("%04d-%02d-%02d", Year, Month, Day) AS FullDate FROM Birthday;

This will produce the result as shown below: