SQLite printf() Function

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

The printf() function is equivalent to the format() function. In SQLite 3.8, printf() has been superseded by format(), which is only retained for compatibility with older versions.

Syntax

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

printf(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 printf() function formats a string according to the specified pattern and returns the formatted string.

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

Examples

Here are some examples of SQLite format() function.

Formatting strings using SQLite printf() 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 two 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 printf() function:

SELECT printf('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 printf() function:

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

This is Lucy. She likes basketball.

Format numbers with the SQLite printf() function

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

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

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

123.46

Padding Strings with SQLite printf() function

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

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

SELECT printf('%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 printf('%-10s', 'a') || '|';

a         |

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