ord

Applies to VAST Cluster 5.5.

Functions › Text basics › Characters and code points. Returns the Unicode code point of the first character of a string, as an integer, or -1 for an empty string. unicode is the same function.

Syntax

ord(s)
unicode(s)

Returns: INTEGER.

Aliases: unicode.

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_ord. 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 five labels: one in ASCII, one that starts with an accented letter, one that starts with a symbol, an empty label and a NULL:

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_ord"'

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}.labels"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, label VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'Invoice'), "
    "(2, 'Échéance'), "
    "(3, '€ amount'), "
    "(4, ''), "
    "(5, NULL)")

[]
[]
[(5,)]

What it does

ord returns the code point of the first character of s: the number that Unicode gives that character. For an empty string, which has no first character, it returns -1.

A code point isn't always what a reader sees as the first character. The French flag is two regional-indicator code points, and 'e\u0301' (displayed as é) is an e followed by a combining accent, so ord returns 101, the code of e. See length for code points and graphemes.

How ord differs from ascii

ascii takes the same argument and, for each non-empty string on this page, returns the same code point as ord. The difference is the empty string:

-1 can't be a code point, so ord(s) = -1 means that s is empty. The 0 that ascii returns for an empty string is also the code point of the NUL character, chr(0). Use ord when you need to tell the two apart.

NULLs and edge cases

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples:

run("SELECT ord(NULL), ord(''), unicode(''), ord('A'), ord('€'), unicode('é'), unicode('😀')")
run("SELECT ord('e\u0301'), ord('🇫🇷')")
run("SELECT ord(123)")
run("SELECT unicode('\\xAA'::BLOB)")

[(None, -1, -1, 65, 8364, 233, 128512)]
[(101, 127467)]
InternalError: Binder Error: No function matches the given name and argument types 'ord(INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'unicode(BLOB)'. You might need to add explicit type casts.

Examples

The first code point of each label, with ord and unicode, and with ascii for comparison. They differ only on the empty label:

run(f"SELECT id, label, ord(label), unicode(label), ascii(label) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'Invoice', 73, 73, 73), (2, 'Échéance', 201, 201, 201), (3, '€ amount', 8364, 8364, 8364), (4, '', -1, -1, 0), (5, None, None, None, None)]

Keep the labels that start with a character outside ASCII. The empty label gives -1, so it isn't kept:

run(f"SELECT id, label FROM {TABLE} WHERE ord(label) > 127 ORDER BY id LIMIT 10")

[(2, 'Échéance'), (3, '€ amount')]

Show the first code point in the usual U+ notation, with printf. %04X is upper-case hexadecimal, at least four digits:

run(f"SELECT id, printf('U+%04X', ord(label)) FROM {TABLE} WHERE ord(label) >= 0 ORDER BY id LIMIT 10")

[(1, 'U+0049'), (2, 'U+00C9'), (3, 'U+20AC')]

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 ord() is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes ord() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes ord() and keeps the rows that match. No rows are skipped in storage.

The plan of the first WHERE example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE ord(label) > 127")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│     (ord(label) > 127)    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           label           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Test for empty strings with = '', not ord(s) = -1. A comparison of the raw column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan for label = '' has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE label = ''")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• Store the first code point if you filter on it often. Compute ord(label) when you write the rows and keep it in its own column: first_code > 127 is pushed down, ord(label) > 127 isn't.
• Combine it with a pushed-down condition. In id > 100 AND ord(label) > 127, id > 100 is still evaluated in VAST DataBase, so ord() runs only on the rows that pass it.

Limitations and errors

• Strings only. A number, a decimal, a BLOB, a date, a boolean or a list fails with No function matches the given name and argument types. Cast it to VARCHAR first.
• The first code point, not the first character a reader sees. A flag or a letter with a combining accent starts with a code point that isn't the whole character. See What it does.
• -1 for an empty string, where ascii returns 0. Check for -1 before you pass the result to chr: chr(-1) fails with Invalid UTF8 Codepoint -1.

run("SELECT ord(DATE '2026-10-16')")
run("SELECT chr(ord(''))")

InternalError: Binder Error: No function matches the given name and argument types 'ord(DATE)'. You might need to add explicit type casts.
OSError: Invalid UTF8 Codepoint -1

Similar functions

The last block also shows the NUL character, chr(0): ord and ascii both return 0 for it. Only ord and unicode then give a different value, -1, for an empty string.

run("SELECT ord('Hello'), unicode('Hello'), ascii('Hello')")
run("SELECT ord('é'), unicode('é'), ascii('é'), chr(233)")
run("SELECT ord('e\u0301'), unicode('e\u0301'), ascii('e\u0301')")
run("SELECT ord(''), unicode(''), ascii(''), ord(chr(0)), ascii(chr(0))")

[(72, 72, 72)]
[(233, 233, 233, 'é')]
[(101, 101, 101)]
[(-1, -1, 0, 0, 0)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Text basics: all the text functions.
• ascii and chr.
• length: code points and graphemes.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.