--- title: "format" slug: "dev-database-reference-sql-functions-string-format" updated: 2026-10-30T18:18:56Z published: 2026-10-30T18:18:56Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-string-format" --- > ## 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. # format *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 template, replacing each `{}` placeholder with the next value. A placeholder can name a position and a format specification, such as a width, an alignment or a number of decimals. `format` has no aliases. ## Syntax ``` format(template, value, ...) ``` | Argument | Type | Description | | --- | --- | --- | | `template` | `VARCHAR` | The text to build, with a `{...}` placeholder for each value. | | `value, ...` | any | The values to put in the placeholders: strings, numbers, dates, booleans, lists, `BLOB` values. | **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_format`. 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_format"' 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 `format` copies `template` and replaces each placeholder with a value, converted to text: - `{}` takes the next value, from left to right. - `{0}`, `{1}`, … take the value at that position, counting from 0, so a value can be used out of order. - `{:spec}` formats the value with a specification after the colon. - `{{` and `}}` are a literal `{` and `}`. Values that the template doesn't use are ignored: `format('no placeholders', 1)` returns `'no placeholders'`. ### Format specifications | Specification | Meaning | Call | Result | | --- | --- | --- | --- | | `{:>8}` `{:<8}` `{:^8}` | right-align, left-align or centre in 8 characters | `format('{:>8}\\|{:<8}\\|{:^8}', 'r', 'l', 'c')` | `' r\\|l \\| c '` | | `{:.2f}` | fixed-point with 2 decimals | `format('{:.2f}', 3.14159)` | `'3.14'` | | `{:08.3f}` | 3 decimals, padded with zeros to 8 characters | `format('{:08.3f}', 3.14159)` | `'0003.142'` | | `{:05d}` | an integer padded with zeros to 5 digits | `format('{:05d}', 42)` | `'00042'` | | `{:+d}` | an integer with its sign, also when positive | `format('{:+d}', 5)` | `'+5'` | | `{:,}` | thousands separators | `format('{:,}', 1234567)` | `'1,234,567'` | | `{:x}` | hexadecimal, lower case | `format('{:x}', 255)` | `'ff'` | | `{:b}` | binary | `format('{:b}', 5)` | `'101'` | | `{:e}` | scientific notation | `format('{:e}', 12345.678)` | `'1.234568e+04'` | | `{} {}` | the next value, in order | `format('{} {}', 'a', 'b')` | `'a b'` | | `{1} {0}` | values by position | `format('{1} {0}', 'a', 'b')` | `'b a'` | | `{{}}` | literal braces | `format('{{}}', 1)` | `'{}'` | A centred value with an odd number of spaces left over gets the extra space on its right, as `' c '` shows. A position and a specification combine: `format('{1:>5}', 'a', 'b')` returns `' b'`. `{:.2f}` works on `DOUBLE` and `DECIMAL` values alike: `format('{:.2f}', 2.5::DOUBLE)` returns `'2.50'`. ### How values are converted With a plain `{}`, every type is accepted and converted to its text form: | Value | `format('[{}]', value)` | | --- | --- | | `'Hello'` | `'[Hello]'` | | `123` | `'[123]'` | | `1.5` | `'[1.5]'` | | `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 | | `'xyz'::BLOB` | `'[xyz]'`: printable bytes are written as characters | A specification must suit the value's type: `{:d}` on a string or on a decimal such as `1.5` fails. See [Limitations and errors](/develop/docs/dev-database-reference-sql-functions-string-format#limitations-and-errors). ## NULLs and edge cases | Input | Call | Result | | --- | --- | --- | | `template` is `NULL` | `format(NULL)` | `NULL` | | `template` is `NULL`, with a value | `format(NULL, 'a')` | `NULL` | | A `NULL` value | `format('{}', NULL)` | `NULL`: the whole result, not an empty placeholder | | `''` as the value | `format('[{}]', '')` | `'[]'` | | More values than placeholders | `format('no placeholders', 1)` | `'no placeholders'`: the extra values are ignored | | Fewer values than placeholders | `format('{} {}', 1)` | Error: `Argument index "1" out of range` | | An unclosed `{` | `format('{', 1)` | Error: `invalid format string` | | A specification that doesn't suit the value | `format('{: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`: `format('{} {}', 'a', NULL)` returns `NULL`. The second block converts the values in [How values are converted](/develop/docs/dev-database-reference-sql-functions-string-format#how-values-are-converted). 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-format#set-up-the-examples): ``` run("SELECT format(NULL), format(NULL, 'a'), format('{}', NULL), format('[{}]', ''), format('no placeholders', 1)") run("SELECT format('[{}]', 123), format('[{}]', 1.5), format('[{}]', DATE '2026-10-16'), format('[{}]', true), format('[{}]', ['a', 'b']), format('[{}]', '\\xAA'::BLOB), format('[{}]', 'xyz'::BLOB)") run("SELECT format('{} {}', 1)") run("SELECT format('{', 1)") run("SELECT format('{:d}', 'x')") ``` ``` [(None, None, None, '[]', 'no placeholders')] [('[123]', '[1.5]', '[2026-10-16]', '[true]', '[[a, b]]', '[\\xAA]', '[xyz]')] OSError: Argument index "1" out of range OSError: invalid format string OSError: Invalid type specifier "d" for formatting a value of type string ``` The specifications from the table above: ``` run("SELECT format('{:>8}|{:<8}|{:^8}', 'r', 'l', 'c'), format('{:.2f}', 3.14159), format('{:08.3f}', 3.14159), format('{:05d}', 42)") run("SELECT format('{:+d}', 5), format('{:,}', 1234567), format('{:x}', 255), format('{:b}', 5), format('{:e}', 12345.678)") run("SELECT format('{} {}', 'a', 'b'), format('{1} {0}', 'a', 'b'), format('{{}}', 1)") ``` ``` [(' r|l | c ', '3.14', '0003.142', '00042')] [('+5', '1,234,567', 'ff', '101', '1.234568e+04')] [('a b', 'b a', '{}')] ``` ## Examples The templates are full of braces, so the next blocks build each statement with `+` rather than a Python f-string, where every brace would have to be doubled. A sentence for each order, with a string, an integer and a date. The row with no values gives `NULL`: ``` run("SELECT id, format('{} ordered {} items on {}', 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. `coalesce` replaces a missing name with `(unknown)`: ``` run("SELECT id, format('{:<14}|{:>4}|{:.2f}', coalesce(customer, '(unknown)'), 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("SELECT id, customer FROM " + TABLE + " WHERE format('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 `format()` is always computed by the Query Engine: - **In the `SELECT` list,** the scan reads the columns and the Query Engine computes `format()` for each row it returns. - **In `WHERE`,** the scan sends the columns for every row, and a `FILTER` step in the Query Engine computes `format()` 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("SELECT id FROM " + TABLE + " WHERE format('ORD-{:05d}', id) = 'ORD-00002'") ``` ``` ┌───────────────────────────┐ │ FILTER │ │ ──────────────────── │ │ (format('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.** `format('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("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 format('{}', customer) = '...'`, `id > 100` is still evaluated in VAST DataBase, so `format()` runs only on the rows that pass it. ## Limitations and errors - **One value per placeholder.** Fewer values than placeholders fails with `Argument index "1" out of range`: placeholders count from 0, so index 1 is the second placeholder. - **Balanced braces.** An unclosed `{` fails with `invalid format string`. Write a literal brace as `{{` or `}}`. - **The specification must suit the type.** `{:d}` on a string fails with `Invalid type specifier "d" for formatting a value of type string`, and on a decimal such as `1.5` with `Invalid type specifier "d" for formatting a value of type float`. Cast the value first, or use a plain `{}`. - **A `NULL` value makes the whole result `NULL`.** Replace it first with [coalesce](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-coalesce). - **`{}` placeholders, not `%` codes.** For C-style `%s` and `%d`, use [printf](/develop/docs/dev-database-reference-sql-functions-string-printf). ``` run("SELECT format('{} and {}', 'a')") run("SELECT format('{:d}', 'x')") run("SELECT format('{:d}', CAST('42' AS BIGINT)), format('{}', coalesce(NULL, '-'))") ``` ``` OSError: Argument index "1" out of range OSError: Invalid type specifier "d" for formatting a value of type string [('42', '-')] ``` ## Similar functions | Function | Template | Builds `00042\\|3.14\\|x` | A `NULL` value | | --- | --- | --- | --- | | `format` | `{}` placeholders | `format('{:05d}\\|{:.2f}\\|{}', 42, 3.14159, 'x')` | the result is `NULL` | | [printf](/develop/docs/dev-database-reference-sql-functions-string-printf) | C-style `%` codes | `printf('%05d\\|%.2f\\|%s', 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'` | `format` accepts any type in a plain `{}`; `printf`'s `%s` doesn't accept numbers. See [printf](/develop/docs/dev-database-reference-sql-functions-string-printf#limitations-and-errors). ``` run("SELECT format('{:05d}|{:.2f}|{}', 42, 3.14159, 'x'), printf('%05d|%.2f|%s', 42, 3.14159, 'x')") run("SELECT format('{}', NULL), printf('%s', 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. - [printf](/develop/docs/dev-database-reference-sql-functions-string-printf): the same job with C-style `%` codes. - [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.