--- title: "to_base" slug: "dev-database-reference-sql-functions-numeric-to-base" updated: 2026-10-30T18:18:51Z published: 2026-10-30T18:18:51Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-numeric-to-base" --- > ## 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. # to_base *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Numeric and math functions](/develop/docs/dev-database-reference-sql-functions-numeric) › Number formatting.* Writes a non-negative integer as text in a base from 2 to 36, with capital letters for the digits above 9. With a third argument, it pads the result with zeros to at least that many digits. ## Syntax ``` to_base(n, radix) to_base(n, radix, min_length) ``` | Argument | Type | Description | | --- | --- | --- | | `n` | An integer type up to `BIGINT` | The number to write. It must be 0 or more. | | `radix` | `INTEGER` | The base, from 2 to 36. | | `min_length` | `INTEGER` | Optional. The minimum number of digits, from 0 to 64; shorter results are padded with leading zeros. | **Returns:** `VARCHAR`. ## 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_numeric_to_base`. 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 five numbers. The last row has no value: ``` 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_numeric_to_base"' 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}.codes" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, n BIGINT)") run(f"INSERT INTO {TABLE} VALUES (1, 255), (2, 1536), (3, 0), (4, 123456789012), (5, NULL)") ``` ``` [] [] [(5,)] ``` ## What it does `to_base(n, radix)` writes `n` in base `radix`, using the digits `0` to `9` and then the capital letters `A` to `Z`, so base 36 uses every digit and letter: `to_base(35, 36)` is `'Z'`. The result has no prefix such as `0x`. | Call | Result | | --- | --- | | `to_base(255, 16)` | `'FF'` | | `to_base(255, 2)` | `'11111111'` | | `to_base(255, 8)` | `'377'` | | `to_base(255, 36)` | `'73'` | | `to_base(255, 16, 8)` | `'000000FF'` | `min_length` is a minimum, not a fixed width: a longer result isn't cut, so `to_base(255, 16, 2)` and `to_base(255, 16, 0)` are both `'FF'`. ## NULLs and edge cases | Input | Result | | --- | --- | | A `NULL` number, radix or `min_length` | `NULL` | | `to_base(0, 2)` | `'0'` | | `to_base(0, 16, 4)` | `'0000'` | | `to_base(255, 16, 2)`, `to_base(255, 16, 0)` | `'FF'` | | `to_base(9223372036854775807, 36)`, the largest `BIGINT` | `'1Y2P0IJ32E8E7'` | | `to_base(255, '16')`, the radix as a string | `'FF'` | | `to_base(5::TINYINT, 2)`, a smaller integer type | `'101'` | | A negative number | Error: `'to_base' number must be greater than or equal to 0` | | A radix below 2 or above 36 | Error: `'to_base' radix must be between 2 and 36` | | A `min_length` below 0 or above 64 | Error: `'to_base' min_length must be between 0 and 64` | | A `DECIMAL`, `DOUBLE`, `UBIGINT` or `HUGEINT` number | Error: `No function matches the given name and argument types` | 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-numeric-to-base#set-up-the-examples): ``` run("SELECT to_base(NULL, 16), to_base(255, NULL), to_base(255, 16, NULL)") run("SELECT to_base(0, 2), to_base(0, 16, 4), to_base(255, 16, 2), to_base(255, 16, 0)") run("SELECT to_base(9223372036854775807, 36), to_base(255, '16'), to_base(5::TINYINT, 2)") run("SELECT to_base(-1, 2)") run("SELECT to_base(5, 37)") run("SELECT to_base(255, 16, 100)") run("SELECT to_base(1.5, 16)") run("SELECT to_base(18446744073709551615::UBIGINT, 16)") ``` ``` [(None, None, None)] [('0', '0000', 'FF', 'FF')] [('1Y2P0IJ32E8E7', 'FF', '101')] OSError: 'to_base' number must be greater than or equal to 0 OSError: 'to_base' radix must be between 2 and 36 OSError: 'to_base' min_length must be between 0 and 64 InternalError: Binder Error: No function matches the given name and argument types 'to_base(DECIMAL(2,1), INTEGER_LITERAL)'. You might need to add explicit type casts. InternalError: Binder Error: No function matches the given name and argument types 'to_base(UBIGINT, INTEGER_LITERAL)'. You might need to add explicit type casts. ``` ## Examples Each number in hexadecimal, binary and base 36: ``` run(f"SELECT id, n, to_base(n, 16), to_base(n, 2), to_base(n, 36) FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, 255, 'FF', '11111111', '73'), (2, 1536, '600', '11000000000', '16O'), (3, 0, '0', '0', '0'), (4, 123456789012, '1CBE991A14', '1110010111110100110010001101000010100', '1KPQZG2C'), (5, None, None, None, None)] ``` Fixed-width hexadecimal codes, padded to eight digits: ``` run(f"SELECT id, to_base(n, 16, 8) FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, '000000FF'), (2, '00000600'), (3, '00000000'), (4, '1CBE991A14'), (5, None)] ``` Keep only the rows whose hexadecimal form is `FF`: ``` run(f"SELECT id, n FROM {TABLE} WHERE to_base(n, 16) = 'FF' ORDER BY id LIMIT 10") ``` ``` [(1, 255)] ``` ## 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 `to_base()` is always computed by the Query Engine: - **In the `SELECT` list,** the scan reads the column and the Query Engine computes `to_base()` for each row it returns. - **In `WHERE`,** the scan sends the column for every row, and a `FILTER` step in the Query Engine computes `to_base()` 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 to_base(n, 16) = 'FF'") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (to_base(n, 16, 0) = 'FF')│ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ n │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Compare the number, not its text.** `n = 255` returns the same rows as `to_base(n, 16) = 'FF'`, and a comparison of the raw column with a constant 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 n = 255") ``` ``` ┌───────────────────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ``` - **Format in the `SELECT` list.** Filter on the raw column, and call `to_base()` only on the rows you return. ## Limitations and errors - **Non-negative numbers only.** A negative number fails with `'to_base' number must be greater than or equal to 0`. [bin](/develop/docs/dev-database-reference-sql-functions-numeric-bin) and [hex](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-hex) write a negative number as its 64-bit two's complement instead. - **Integers up to `BIGINT`.** A `DECIMAL`, `DOUBLE` or `FLOAT` fails with `No function matches the given name and argument types`, and so do `UBIGINT` and `HUGEINT`. Cast the value to `BIGINT` first: `to_base(CAST(x AS BIGINT), 16)`. - **Capital letters.** Wrap it in `lower()` for lowercase digits: `lower(to_base(255, 16))` is `'ff'`. - **No function to convert back.** `from_base` and `conv` fail with `Scalar Function with name ... does not exist!`. For hexadecimal, cast the text with a `0x` prefix: `('0x' || 'FF')::INTEGER` is `255`. ``` run("SELECT to_base(CAST(255.0 AS BIGINT), 16), lower(to_base(255, 16))") run("SELECT from_base('FF', 16)") run("SELECT ('0x' || 'FF')::INTEGER") ``` ``` [('FF', 'ff')] InternalError: Catalog Error: Scalar Function with name from_base does not exist! [(255,)] ``` ## Similar functions | Function | Writes | Result for `10` | Result for `255` | Result for `-10` | | --- | --- | --- | --- | --- | | `to_base(n, 2)`, `to_base(n, 16)` | any base from 2 to 36 | `'1010'` (base 2) | `'FF'` (base 16) | error | | [bin](/develop/docs/dev-database-reference-sql-functions-numeric-bin), also `to_binary` | binary | `'1010'` | | 64-bit two's complement | | [hex](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-hex), also `to_hex` | hexadecimal | | `'FF'` | `'FFFFFFFFFFFFFFF6'` | ``` run("SELECT bin(10), to_binary(10), to_base(10, 2), hex(255), to_hex(255), to_base(255, 16), to_base(255, 16, 4)") run("SELECT bin(-10), hex(-10)") run("SELECT to_base(-10, 2)") ``` ``` [('1010', '1010', '1010', 'FF', 'FF', 'FF', '00FF')] [('1111111111111111111111111111111111111111111111111111111111110110', 'FFFFFFFFFFFFFFF6')] OSError: 'to_base' number must be greater than or equal to 0 ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [Numeric and math functions](/develop/docs/dev-database-reference-sql-functions-numeric): all the numeric functions. - [bin](/develop/docs/dev-database-reference-sql-functions-numeric-bin) and [hex](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-hex). - [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.