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.
SELECT printf('%s costs %.2f', name, price) AS label FROM products;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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. |
-- 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 (3becomes3.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,printfdoes not returnNULLwhen an argument isNULL. Check withCOALESCE()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.
%qand%Qexist for escaping, but parameterized queries are the right way to pass values from user input.