ascii

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. Despite its name, it isn't limited to ASCII. ascii has no aliases; ord (also unicode) is the same except on an empty string.

Syntax

ascii(s)

Returns: INTEGER.

Aliases: none.

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_ascii. 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 names: one that starts with a capital letter, one in lower case, one that starts with an accented letter, an empty name 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_ascii"'

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}.people"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, name VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'Ada Lovelace'), "
    "(2, 'grace hopper'), "
    "(3, 'Émile Borel'), "
    "(4, ''), "
    "(5, NULL)")

[]
[]
[(5,)]

What it does

ascii returns the code point of the first character of s: the number that Unicode gives that character. For ASCII characters it's the ASCII code; for other characters it's their Unicode code point, so the result can be larger than 127.

A code point isn't always what a reader sees as the first character. 'e\u0301' is displayed as é but starts with a plain e, so ascii returns 101, the code of e. To make such strings start with one precomposed character where one exists, apply nfc_normalize first.

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 ascii(NULL), ascii(''), ascii('A'), ascii('AB'), ascii('é'), ascii('€'), ascii('e\u0301')")
run("SELECT ascii(nfc_normalize('e\u0301'))")
run("SELECT ascii(123)")
run("SELECT ascii('\\xAA'::BLOB)")

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

Examples

The code point of the first character of each name. The empty name gives 0, and NULL gives NULL:

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

[(1, 'Ada Lovelace', 65), (2, 'grace hopper', 103), (3, 'Émile Borel', 201), (4, '', 0), (5, None, None)]

Keep the names that start with a capital ASCII letter, A to Z (codes 65 to 90). grace hopper starts with a lower-case letter and Émile Borel with a letter outside ASCII, so neither matches:

run(f"SELECT id, name FROM {TABLE} WHERE ascii(name) BETWEEN 65 AND 90 ORDER BY id LIMIT 10")

[(1, 'Ada Lovelace')]

Keep the names that start with a character outside ASCII:

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

[(3, 'Émile Borel')]

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

• In the SELECT list, the scan reads the column and the Query Engine computes ascii() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes ascii() 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 ascii(name) BETWEEN 65 AND 90")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (ascii(name) BETWEEN 65  │
│           AND 90)         │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            name           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Test for empty strings with = '', not ascii(s) = 0. 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 name = '' has no FILTER step:

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

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

• Store the first code point if you filter on it often. Compute ascii(name) when you write the rows and keep it in its own column: initial_code BETWEEN 65 AND 90 is pushed down, ascii(name) BETWEEN 65 AND 90 isn't.
• Combine it with a pushed-down condition. In id > 100 AND ascii(name) > 127, id > 100 is still evaluated in VAST DataBase, so ascii() 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.
• Not limited to ASCII. ascii returns any Unicode code point, such as 8364 for €. To test whether a string starts with an ASCII character, compare the result with 127.
• The first code point, not the first character a reader sees. See What it does.
• 0 for an empty string. 0 is also the code point of the NUL character, so ascii can't tell an empty string from a string that starts with NUL. ord returns -1 for an empty string.

run("SELECT ascii(DATE '2026-10-16')")

InternalError: Binder Error: No function matches the given name and argument types 'ascii(DATE)'. You might need to add explicit type casts.

Similar functions

ascii(chr(0)), the NUL character, also returns 0, the same as ascii(''). ord returns 0 for NUL and -1 for an empty string, so use ord when you need to tell them apart.

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

[(72, 72, 72)]
[(233, 233, 233, 'é')]
[(101, 101, 101)]
[(0, -1, -1, 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.
• ord and chr.
• length: code points and graphemes.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.