--- title: "printf" slug: "dev-database-reference-sql-functions-string-printf" updated: 2026-10-30T18:18:56Z published: 2026-10-30T18:18:56Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-string-printf" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # printf *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Text basics](/develop/docs/dev-database-reference-sql-functions-string-text-basics) › Formatting.* Builds a string from a C-style template, replacing each `%` conversion, such as `%s` or `%05d`, with the next value. `printf` has no aliases; `sprintf` isn't available. ## Syntax ``` printf(template, value, ...) ``` | Argument | Type | Description | | --- | --- | --- | | `template` | `VARCHAR` | The text to build, with a `%` conversion for each value. | | `value, ...` | any type its conversion accepts | The values for the conversions, in order. | **Returns:** `VARCHAR`. **Aliases:** none. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_printf`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), and run the blocks in order in one Python session. Below each block is the output you should see. This block connects, defines the `run()` and `plan()` helpers, and creates a table of four orders. The last row has no values: ``` import os import re import adbc_driver_manager import adbc_driver_manager.dbapi as dbapi import adbc_driver_vastdb BUCKET = os.environ["VASTDB_BUCKET"] SCHEMA = f'"{BUCKET}/docs_sql_fn_printf"' conn = dbapi.connect( driver=adbc_driver_vastdb.get_driver_path(), db_kwargs={ "vast.db.endpoint": os.environ["QE_ENDPOINT"], "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"], "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"], }, autocommit=True, ) def server_message(e): """The server's message in a driver error, without the request ID or the echoed statement.""" text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e)) found = (re.search(r'message: "(.*?)", request_id', text, re.S) or re.search(r"description: (.*)\)", text, re.S)) message = found.group(1) if found else text.strip().splitlines()[-1] message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S) message = re.split(r"\\n|\n", message)[0].replace('\\"', '"') return message.replace(BUCKET, "") def run(sql): """Run one statement. Print the rows it returns, or the error.""" try: with conn.cursor() as cur: cur.execute(sql) rows = cur.fetchall() if cur.description else [] except (adbc_driver_manager.Error, OSError) as e: print(f"{type(e).__name__}: {server_message(e)}") else: print(rows) def plan(sql): """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert.""" with conn.cursor() as cur: cur.execute("EXPLAIN " + sql) steps = cur.fetchall()[0][1] print("\n".join(line for line in steps.splitlines() if "Rows" not in line)) TABLE = f"{SCHEMA}.orders" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, customer VARCHAR, qty BIGINT, amount DECIMAL(10, 2), ordered DATE)") run(f"INSERT INTO {TABLE} VALUES " "(1, 'Ada Lovelace', 3, 1234.50, DATE '2026-10-01'), " "(2, 'Grace Hopper', 12, 89.99, DATE '2026-10-02'), " "(3, 'José Martí', 1, 15.00, DATE '2026-10-05'), " "(4, NULL, NULL, NULL, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does `printf` copies `template` and replaces each conversion with the next value, from left to right. A conversion is `%`, optional flags, an optional width, an optional precision after a `.`, and a letter that says how to write the value. `%%` is a literal `%`. ### Conversions | Conversion | Meaning | Call | Result | | --- | --- | --- | --- | | `%s` | a string, written as it is | `printf('[%s]', 'Hello')` | `'[Hello]'` | | `%-6s` `%5s` | a string left-aligned in 6 characters, or right-aligned in 5 | `printf('%-6s\\|%5s\\|', 'ab', 'cd')` | `'ab \\| cd\\|'` | | `%.3s` | at most 3 characters of a string | `printf('%.3s', 'abcdef')` | `'abc'` | | `%d`, `%05d` | an integer; `%05d` pads it with zeros to 5 digits | `printf('%05d', 42)` | `'00042'` | | `%i` | the same as `%d` | `printf('%i', 1)` | `'1'` | | `%+d` | an integer with its sign, also when positive | `printf('%+d', 5)` | `'+5'` | | `%,d` | an integer with thousands separators | `printf('%,d', 1234567)` | `'1,234,567'` | | `%.2f` | fixed-point with 2 decimals | `printf('%.2f', 3.14159)` | `'3.14'` | | `%5.1f` | 1 decimal, right-aligned in 5 characters | `printf('%5.1f\\|%-8.3e', 3.14159, 2.5)` | `' 3.1\\|2.500e+00'` | | `%e` `%-8.3e` | scientific notation; with 3 decimals, left-aligned in 8 | `printf('%e', 12345.678)` | `'1.234568e+04'` | | `%g` | a decimal in the shorter of fixed-point and scientific notation | `printf('%g', 0.5)` | `'0.5'` | | `%x` `%X` `%o` | hexadecimal in lower or upper case, and octal | `printf('%x %X %o', 255, 255, 8)` | `'ff FF 10'` | | `%04X` | upper-case hexadecimal, padded with zeros to 4 digits | `printf('%04X', 233)` | `'00E9'` | | `%c` | the character with that code point | `printf('%c', 65)` | `'A'` | | `%%` | a literal `%` | `printf('%%')` | `'%'` | | `%1$s` | the value at a position, counting from 1 | `printf('%1$s', 'a')` | `'a'` | A width is a minimum: `%-8.3e` on 2.5 gives `2.500e+00`, nine characters, with no padding. ### Which values each conversion accepts `%s` accepts strings, dates, booleans, lists and `BLOB` values, but not numbers. Write integers with `%d` and decimals with `%f` or `%e`. The number conversions don't mix: `%f` on an integer such as `42` fails with `Invalid type specifier "f" for formatting a value of type int`, and `%d` on a decimal such as `1.5` with `Invalid type specifier "d" for formatting a value of type float`: | Value | `printf('[%s]', value)` | | --- | --- | | `'Hello'` | `'[Hello]'` | | `DATE '2026-10-16'` | `'[2026-10-16]'` | | `true` | `'[true]'` | | `['a', 'b']` | `'[[a, b]]'`: list elements aren't quoted | | `'\xAA'::BLOB` | `'[\xAA]'`: a byte that isn't a printable ASCII character is written as `\x` and two hexadecimal digits | | `123` | Error: `Invalid type specifier "s" for formatting a value of type int` | | `1.5` | Error: `Invalid type specifier "s" for formatting a value of type float` | `%d` on a string fails the same way: `Invalid type specifier "d" for formatting a value of type string`. ## NULLs and edge cases | Input | Call | Result | | --- | --- | --- | | `template` is `NULL` | `printf(NULL)` | `NULL` | | A `NULL` value | `printf('%s', NULL)` | `NULL`: the whole result, not an empty conversion | | `''` as the value | `printf('[%s]', '')` | `'[]'` | | A date for `%s` | `printf('[%s]', DATE '2026-10-16')` | `'[2026-10-16]'` | | A boolean for `%s` | `printf('[%s]', true)` | `'[true]'` | | A list for `%s` | `printf('[%s]', ['a', 'b'])` | `'[[a, b]]'` | | A `BLOB` for `%s` | `printf('[%s]', '\xAA'::BLOB)` | `'[\xAA]'` | | Fewer values than conversions | `printf('%s %s', 'a')` | Error: `Argument index "1" out of range` | | An integer for `%s` | `printf('[%s]', 123)` | Error: `Invalid type specifier "s" for formatting a value of type int` | | A decimal for `%s` | `printf('[%s]', 1.5)` | Error: `Invalid type specifier "s" for formatting a value of type float` | | A string for `%d` | `printf('%d', 'x')` | Error: `Invalid type specifier "d" for formatting a value of type string` | A `NULL` among several values also makes the whole result `NULL`: `printf('%s %s', 'a', NULL)` returns `NULL`. The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-string-printf#set-up-the-examples): ``` run("SELECT printf(NULL), printf('%s', NULL), printf('[%s]', ''), printf('[%s]', DATE '2026-10-16'), printf('[%s]', true), printf('[%s]', ['a', 'b']), printf('[%s]', '\\xAA'::BLOB)") run("SELECT printf('%s %s', 'a')") run("SELECT printf('[%s]', 123)") run("SELECT printf('[%s]', 1.5)") run("SELECT printf('%d', 'x')") ``` ``` [(None, None, '[]', '[2026-10-16]', '[true]', '[[a, b]]', '[\\xAA]')] OSError: Argument index "1" out of range OSError: Invalid type specifier "s" for formatting a value of type int OSError: Invalid type specifier "s" for formatting a value of type float OSError: Invalid type specifier "d" for formatting a value of type string ``` The conversions from the table above: ``` run("SELECT printf('%-6s|%5s|', 'ab', 'cd'), printf('%.3s', 'abcdef'), printf('%05d', 42), printf('%,d', 1234567)") run("SELECT printf('%.2f', 3.14159), printf('%5.1f|%-8.3e', 3.14159, 2.5), printf('%e', 12345.678)") run("SELECT printf('%x %X %o', 255, 255, 8), printf('%c', 65), printf('%%')") ``` ``` [('ab | cd|', 'abc', '00042', '1,234,567')] [('3.14', ' 3.1|2.500e+00', '1.234568e+04')] [('ff FF 10', 'A', '%')] ``` ## Examples A sentence for each order: `%s` for the name and the date, `%d` for the quantity. The row with no values gives `NULL`: ``` run(f"SELECT id, printf('%s ordered %d items on %s', customer, qty, ordered) FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, 'Ada Lovelace ordered 3 items on 2026-10-01'), (2, 'Grace Hopper ordered 12 items on 2026-10-02'), (3, 'José Martí ordered 1 items on 2026-10-05'), (4, None)] ``` Aligned columns for a fixed-width report: the name left-aligned in 14 characters, the quantity right-aligned in 4, and the amount with two decimals, right-aligned in 10: ``` run(f"SELECT id, printf('%-14s|%4d|%10.2f', customer, qty, amount) FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, 'Ada Lovelace | 3| 1234.50'), (2, 'Grace Hopper | 12| 89.99'), (3, 'José Martí | 1| 15.00'), (4, None)] ``` Find an order by its display code, an id padded with zeros to five digits: ``` run(f"SELECT id, customer FROM {TABLE} WHERE printf('ORD-%05d', id) = 'ORD-00002' ORDER BY id LIMIT 10") ``` ``` [(2, 'Grace Hopper')] ``` ## How it runs in VAST The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (`VAST_SCAN`). VAST DataBase can evaluate a comparison of a column with a constant, but not a function of a column, so `printf()` is always computed by the Query Engine: - **In the `SELECT` list,** the scan reads the columns and the Query Engine computes `printf()` for each row it returns. - **In `WHERE`,** the scan sends the columns for every row, and a `FILTER` step in the Query Engine computes `printf()` and keeps the rows that match. No rows are skipped in storage. The plan of the `WHERE` example shows the `FILTER` step above the scan: ``` plan(f"SELECT id FROM {TABLE} WHERE printf('ORD-%05d', id) = 'ORD-00002'") ``` ``` ┌───────────────────────────┐ │ FILTER │ │ ──────────────────── │ │ (printf('ORD-%05d', id) = │ │ 'ORD-00002') │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Filter on the raw value, and format only for display.** `printf('ORD-%05d', id) = 'ORD-00002'` keeps the same row as `id = 2`, but only the second is a comparison of a raw column with a constant, which is pushed down to VAST DataBase, so rows that don't match never leave storage. Its plan has no `FILTER` step: ``` plan(f"SELECT id FROM {TABLE} WHERE id = 2") ``` ``` ┌───────────────────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ``` - **Combine it with a pushed-down condition.** In `id > 100 AND printf('%s', customer) = '...'`, `id > 100` is still evaluated in VAST DataBase, so `printf()` runs only on the rows that pass it. ## Limitations and errors - **`%s` doesn't accept numbers.** An integer fails with `Invalid type specifier "s" for formatting a value of type int`, and a decimal with `... of type float`. A `DOUBLE`, such as `2.5::DOUBLE`, fails the same way. Use `%d` for integers and `%f` for decimals, cast the number to `VARCHAR`, or use [format](/develop/docs/dev-database-reference-sql-functions-string-format), whose `{}` accepts any type. - **The value must suit the conversion.** A string for `%d` fails with `Invalid type specifier "d" for formatting a value of type string`. `%d` doesn't accept decimals (`... of type float`) and `%f` doesn't accept integers (`... of type int`). Use the conversion that matches the value's type, or [format](/develop/docs/dev-database-reference-sql-functions-string-format) with a plain `{}`. - **One value per conversion.** Fewer values than conversions fails with `Argument index "1" out of range`: values count from 0, so index 1 is the second value. - **A `NULL` value makes the whole result `NULL`.** Replace it first with [coalesce](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-coalesce). - **`sprintf` isn't available.** It fails with `Scalar Function with name sprintf does not exist!`. Use `printf`. ``` run("SELECT printf('%d', 123), printf('%s', CAST(123 AS VARCHAR))") run("SELECT sprintf('%s', 'a')") ``` ``` [('123', '123')] InternalError: Catalog Error: Scalar Function with name sprintf does not exist! ``` ## Similar functions | Function | Template | Builds `00042\\|3.14\\|x` | A `NULL` value | | --- | --- | --- | --- | | `printf` | C-style `%` codes | `printf('%05d\\|%.2f\\|%s', 42, 3.14159, 'x')` | the result is `NULL` | | [format](/develop/docs/dev-database-reference-sql-functions-string-format) | `{}` placeholders; `{}` accepts any type | `format('{:05d}\\|{:.2f}\\|{}', 42, 3.14159, 'x')` | the result is `NULL` | | [concat](/develop/docs/dev-database-reference-sql-functions-string-concat) | none: joins its arguments | | skipped: `concat('a', NULL, 'b')` is `'ab'` | | [\|\| (concatenation)](/develop/docs/dev-database-reference-sql-functions-string-concat-operator) | none: joins two values | | the result is `NULL` | | [concat_ws](/develop/docs/dev-database-reference-sql-functions-string-concat-ws) | none: joins with a separator | | skipped: `concat_ws('-', 'a', NULL, 'b')` is `'a-b'` | ``` run("SELECT printf('%05d|%.2f|%s', 42, 3.14159, 'x'), format('{:05d}|{:.2f}|{}', 42, 3.14159, 'x')") run("SELECT printf('%s', NULL), format('{}', NULL), concat('a', NULL, 'b'), 'a' || NULL || 'b', concat_ws('-', 'a', NULL, 'b')") ``` ``` [('00042|3.14|x', '00042|3.14|x')] [(None, None, 'ab', None, 'a-b')] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [Text basics](/develop/docs/dev-database-reference-sql-functions-string-text-basics): all the text functions. - [format](/develop/docs/dev-database-reference-sql-functions-string-format): the same job with `{}` placeholders. - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.