--- title: "hex" slug: "dev-database-reference-sql-functions-binary-hash-uuid-hex" updated: 2026-10-30T18:18:51Z published: 2026-10-30T18:18:51Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-binary-hash-uuid-hex" --- > ## 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. # hex *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Binary, hash and UUID functions](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid) › Binary data and encodings.* Returns a `BLOB`, the UTF-8 bytes of a `VARCHAR`, or an integer as hexadecimal text in capitals. `to_hex` is the same function. To turn the text back into bytes, use [from_hex](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-from-hex). ## Syntax ``` hex(x) to_hex(x) ``` | Argument | Type | Description | | --- | --- | --- | | `x` | `BLOB`, `VARCHAR` or an integer (`TINYINT` to `BIGINT`, `UBIGINT`, `HUGEINT`, `UHUGEINT`, `VARINT`) | The value to write in hexadecimal. | **Returns:** `VARCHAR`. **Aliases:** `to_hex`. ## 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_binary_hash_uuid_hex`. 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 files, each with a name, its first bytes and a number. 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_binary_hash_uuid_hex"' 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}.files" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, name VARCHAR, data BLOB, n BIGINT)") run(f"INSERT INTO {TABLE} VALUES " "(1, 'hello', '\\x25\\x50\\x44\\x46'::BLOB, 255), " "(2, '', ''::BLOB, 0), " "(3, 'h\u00e9', '\\x00\\x0F'::BLOB, -1), " "(4, NULL, NULL, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does `hex` writes its argument in base 16, with the digits `A` to `F` in capitals. What it writes depends on the type: | Type | What is written | Example | | --- | --- | --- | | `BLOB` | Two digits for every byte, leading zeros kept | `hex('\x00\x0F'::BLOB)` gives `'000F'` | | `VARCHAR` | The string's UTF-8 bytes, two digits each | `hex('hello')` gives `'68656C6C6F'`; `hex('é')` gives `'C3A9'` | | An integer | The number, without leading zeros | `hex(255)` gives `'FF'`, `hex(16)` gives `'10'`, `hex(0)` gives `'0'` | | A negative integer | Its two's complement: 64 bits for `TINYINT` to `BIGINT`, 128 bits for `HUGEINT` | `hex(-1)` and `hex(-1::TINYINT)` give 16 `F`s; `hex(-1::HUGEINT)` gives 32 | `TINYINT`, `SMALLINT` and `INTEGER` values are widened to `BIGINT` first, so a negative one is written with 16 digits, not 2, 4 or 8: a `TINYINT` column holding -128 gives `'FFFFFFFFFFFFFF80'`. ## NULLs and edge cases | Input | Result | | --- | --- | | `NULL` | `NULL` | | `''` (empty string) or `''::BLOB` | `''` | | `hex(0)` | `'0'` | | `hex(-1)` | `'FFFFFFFFFFFFFFFF'` | | `hex(9223372036854775807)` | `'7FFFFFFFFFFFFFFF'` | | `hex(-9223372036854775808)` | `'8000000000000000'` | | `hex(-1::HUGEINT)` | `'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'` | | `hex(42::VARINT)` | `'8000012A'`, not `'2A'`: see [Limitations and errors](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-hex#limitations-and-errors) | | A `DOUBLE`, `DECIMAL`, `BOOLEAN`, `DATE`, `UUID`, `BIT` or list | Error: `No function matches the given name and argument types` | The same cases, as the cluster returns them. They use the helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-hex#set-up-the-examples): ``` run("SELECT hex(NULL), hex(''), hex(''::BLOB), hex(0), hex(-1)") run("SELECT hex(9223372036854775807), hex(-9223372036854775808), hex(-1::HUGEINT), hex(42::VARINT)") run("SELECT hex(2.5::DOUBLE)") run("SELECT hex(true)") ``` ``` [(None, '', '', '0', 'FFFFFFFFFFFFFFFF')] [('7FFFFFFFFFFFFFFF', '8000000000000000', 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF', '8000012A')] InternalError: Binder Error: No function matches the given name and argument types 'hex(DOUBLE)'. You might need to add explicit type casts. InternalError: Binder Error: No function matches the given name and argument types 'hex(BOOLEAN)'. You might need to add explicit type casts. ``` ## Examples The name, the first bytes and the number of each file in hexadecimal. `to_hex` gives the same results: ``` run(f"SELECT id, hex(name), hex(data), to_hex(n) FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, '68656C6C6F', '25504446', 'FF'), (2, '', '', '0'), (3, '68C3A9', '000F', 'FFFFFFFFFFFFFFFF'), (4, None, None, None)] ``` Find the PDF files, whose first bytes are `25504446` (`%PDF`): ``` run(f"SELECT id, name FROM {TABLE} WHERE hex(data) = '25504446' ORDER BY id LIMIT 10") ``` ``` [(1, 'hello')] ``` ## 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`). `hex()` is computed by the Query Engine: - **In the `SELECT` list,** the scan reads the column and the Query Engine computes `hex()` for each row it returns. - **In `WHERE`,** the scan sends the column for every row, and a `FILTER` step in the Query Engine computes `hex()` 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 hex(data) = '25504446'") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (hex(data) = '25504446') │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ data │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Turn the constant into bytes, not the column into text.** `data = from_hex('25504446')` compares the raw `BLOB` column with a constant: the Query Engine computes `from_hex()` once, and the comparison is pushed down to VAST DataBase. The plan has no `FILTER` step: ``` plan(f"SELECT id FROM {TABLE} WHERE data = from_hex('25504446')") ``` ``` ┌───────────────────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ``` - **Write the bytes as a `BLOB` constant** for the same effect: `data = '\x25\x50\x44\x46'::BLOB` is pushed down too. ## Limitations and errors - **Bytes, strings and integers only.** A `DOUBLE`, a `DECIMAL`, a `BOOLEAN`, a `DATE`, a `UUID`, a `BIT` value or a list fails with `No function matches the given name and argument types`, and the error lists the types `hex` accepts. Cast a `DECIMAL` or `DOUBLE` to an integer, or to `VARCHAR` for the hex of its text. - **`VARINT` isn't written as a plain number.** `hex(42::VARINT)` gives `'8000012A'`, while `hex(42)` gives `'2A'`. Cast a `VARINT` to `BIGINT` or `HUGEINT` first when it fits. - **Capitals only.** The result uses `A` to `F`. To compare it with lower-case hex, such as the output of [md5](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-md5), apply `lower()` to one side. - **No `0x` prefix and no separators.** Add them with `||` if you need them. ``` run("SELECT hex(42), hex(42::VARINT), hex(42::HUGEINT)") run("SELECT hex(DATE '2026-10-16')") ``` ``` [('2A', '8000012A', '2A')] InternalError: Binder Error: No function matches the given name and argument types 'hex(DATE)'. You might need to add explicit type casts. ``` ## Similar functions | Function | What it gives for `'hé'` | | --- | --- | | `hex` | `'68C3A9'`: hexadecimal text of the UTF-8 bytes | | [from_hex](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-from-hex) | The reverse: `from_hex('68C3A9')` gives the bytes back | | [to_base64](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-to-base64) (of `encode('hé')`) | `'aMOp'`: Base64 text, shorter than hex | | [bin](/develop/docs/dev-database-reference-sql-functions-string-bin) | `'011010001100001110101001'`: the bits as text | | [encode](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-encode) | `b'h\xc3\xa9'`: the UTF-8 bytes as a `BLOB` | ``` run("SELECT hex('h\u00e9'), decode(from_hex('68C3A9')), to_base64(encode('h\u00e9')), bin('h\u00e9'), encode('h\u00e9')") ``` ``` [('68C3A9', 'hé', 'aMOp', '011010001100001110101001', b'h\xc3\xa9')] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [Binary, hash and UUID functions](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid): all the functions in this family. - [Data types in SQL](/develop/docs/dev-database-reference-sql-data-types): `BLOB` columns. - [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.