Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Scalar Functions

SQL FORMAT() and printf() functions

Reference for SQL text formatting with printf() and FORMAT(): placeholders, padding, decimals, NULL handling and how SQLite, PostgreSQL, MySQL and SQL Server differ.

printf(template, value, ...) builds a text value from a template that contains placeholders such as %d (integer), %s (text) and %.2f (a number with two decimals). It is how SQLite produces fixed decimals, zero-padded numbers and aligned columns. SQLite also accepts the name format() for the same function.

Beware of the name: FORMAT means something different in every database. In SQL Server and MySQL it formats a single number or date, while PostgreSQL's format() is a %s-style template function. The ideas are the same everywhere; the syntax is not.

SQL
SELECT printf('%s costs %.2f', name, price) AS label
FROM products;

Try it

SQL Fixed decimals and padding ยท database: shop

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

SQL Placeholders, NULL and percent signs

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

Syntax

SQL
printf(template [, value1, value2, ...])
format(template [, value1, value2, ...])
Name Description
template Text containing literal characters and placeholders. Each placeholder consumes the next value.
value The values to insert, in order.

Common placeholders in SQLite:

Placeholder Meaning
%d Integer (a real number is truncated toward zero)
%f, %.2f Floating-point number; .2 is the number of decimals (default 6)
%s Text
%x, %X Integer in hexadecimal
%5d, %-5d, %05d Minimum width 5; left-aligned with -; padded with zeros with 0
%q, %Q Text escaped for use inside an SQL string literal (%Q also adds the quotes and turns NULL into the word NULL)
%% A literal percent sign

Returns: text. A missing value for a placeholder is treated as NULL. A NULL argument is printed as an empty string for %s and as 0 for %d; it never makes the whole result NULL.

Dialect differences

Database Function Notes
SQLite printf(fmt, ...), format(fmt, ...) C-style placeholders as above.
PostgreSQL format('%s has %s', a, b); to_char(num, '999,990.00') format() supports %s, %I (identifier) and %L (literal). Numbers and dates are formatted with to_char().
MySQL FORMAT(number, decimals), CONCAT(), LPAD() FORMAT(1234.5, 2) returns '1,234.50' with thousands separators. There is no template function.
SQL Server FORMAT(value, 'N2'), CONCAT(), STR() .NET-style format strings, for example 'N2', 'C', 'yyyy-MM-dd'. Slow on large result sets.
Oracle TO_CHAR(number, '9990.00') Format models instead of templates.
SQL
-- PostgreSQL
SELECT format('%s costs %s', name, to_char(price, 'FM999990.00')) FROM products;

-- MySQL
SELECT CONCAT(name, ' costs ', FORMAT(price, 2)) FROM products;

-- SQL Server
SELECT CONCAT(name, ' costs ', FORMAT(price, 'N2')) FROM products;

Usage notes

  • Rounding vs formatting. ROUND() changes the number; printf('%.2f', x) produces text that always shows two decimals (3 becomes 3.00).
  • The result is text. Do not sort or add formatted numbers: '10.00' sorts before '9.00' as text. Format at the very end of a query.
  • NULL is quiet. Unlike the || operator, printf does not return NULL when an argument is NULL. Check with COALESCE() if a blank is not what you want.
  • Thousands separators are not available in SQLite's printf. Use the vendor functions above in other databases or format in your application.
  • Padding by character count. Widths count bytes in some databases; for non-ASCII text, LPAD() / RPAD() or application code is safer.
  • Building SQL. %q and %Q exist for escaping, but parameterized queries are the right way to pass values from user input.