hex

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › 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.

Syntax

hex(x)
to_hex(x)

Returns: VARCHAR.

Aliases: to_hex.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (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), 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, "<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:

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

The same cases, as the cluster returns them. They use the helpers from 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, 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

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: all the functions in this family.
• Data types in SQL: BLOB columns.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.