SQLite format() Function

The SQLite format() function formats a string according to the specified pattern, which is similar to the printf function.

The SQLite printf() function is equivalent to format().

Syntax

Here is the syntax of the SQLite format() function:

format(pattern[, arg1, arg2, ...])

Parameters

pattern

Required. The format string. You can use some placeholders in it like: %s, %z, %X, %f etc.

arg1, arg2, ...

Optional. The arguments to replace placeholders in format strings.

Return value

The SQLite format() function formats a string according to the specified pattern and returns the formatted string.

For example, format('%s %s', 'hello', 'world') returns hello world.

Examples

Here are some examples of SQLite format() function.

Formatting strings using SQLite format() function

For example, when you introduce a person in English, you usually say: “This is Tim. He likes football.”, but for another person, you might say: “This is Lucy. She likes basketball.”.

For a more methodical output, we can abstract these similar sentences into a general pattern: This is %s. %s likes %s.. Here we use 3 placeholders %s:

• The first %s represents a name.
• The second %s represents He or She.
• The second %s represents a hobby.

If you want to output a statement introducing Tim, you can use the following statement with the SQLite format() function:

SELECT format('This is %s. %s likes %s.', 'Tim', 'He', 'football');

This is Tim. He likes football.

If you want to output a statement introducing Lucy, you can use the following statement with the SQLite format() function:

SELECT format('This is %s. %s likes %s.', 'Lucy', 'She', 'basketball');

This is Lucy. She likes basketball.

Format numbers with the SQLite format() function

You can also format numbers using the SQLite format() function.

For example, if you want to keep a number with 2 decimal places, use the following statement with the SQLite format() function:

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

123.46

Padding Strings with SQLite format() function

You can also pad strings to a certain length using the SQLite format() function.

If you want to left pad with spaces to a to make its length to 10, use the following statement like this:

SELECT format('%10s', 'a');

         a

If you want to right pad with spaces to a to make its length to 10, use the following statement like this:

SELECT format('%-10s', 'a') || '|';

a         |

In order to make the output more intuitive, | is added at the end of the output string.