chr

Applies to VAST Cluster 5.5.

Functions › Text basics › Characters and code points. Returns the one-character string whose Unicode code point is the given integer. It's the reverse of ord. chr has no aliases.

Syntax

chr(code_point)

Returns: VARCHAR, one character long.

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_chr. 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 code points with their names. The code column is an INTEGER, the type chr takes. 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_chr"'

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}.code_points"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, code INTEGER, name VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 65, 'LATIN CAPITAL LETTER A'), "
    "(2, 233, 'LATIN SMALL LETTER E WITH ACUTE'), "
    "(3, 8364, 'EURO SIGN'), "
    "(4, 128512, 'GRINNING FACE'), "
    "(5, NULL, NULL)")

[]
[]
[(5,)]

What it does

chr returns the character that Unicode gives the number code_point. For 0 to 127 that's the ASCII character with that code; above 127 it's any Unicode character, up to 1114111 (U+10FFFF), the highest code point.

chr returns one code point, which isn't always a whole character as a reader sees it: a flag, for example, is two code points. Build such a string with chr(127467) || chr(127479), or write it as a literal.

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 chr(NULL), chr(65), chr(233), chr(8364), chr(128512)")
run("SELECT chr(0), length(chr(0)), chr(1114111), chr('65')")
run("SELECT chr(-1)")
run("SELECT chr(55296)")
run("SELECT chr(1114112)")
run("SELECT chr(65.0)")

[(None, 'A', 'é', '€', '😀')]
[('\x00', 1, '\U0010ffff', 'A')]
OSError: Invalid UTF8 Codepoint -1
OSError: Invalid UTF8 Codepoint 55296
OSError: Invalid UTF8 Codepoint 1114112
InternalError: Binder Error: No function matches the given name and argument types 'chr(DECIMAL(3,1))'. You might need to add explicit type casts.

Examples

The character for each code point:

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

[(1, 65, 'A', 'LATIN CAPITAL LETTER A'), (2, 233, 'é', 'LATIN SMALL LETTER E WITH ACUTE'), (3, 8364, '€', 'EURO SIGN'), (4, 128512, '😀', 'GRINNING FACE'), (5, None, None, None)]

Build a label from a character and its name. || returns NULL when either side is NULL:

run(f"SELECT id, chr(code) || ' ' || lower(name) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'A latin capital letter a'), (2, 'é latin small letter e with acute'), (3, '€ euro sign'), (4, '😀 grinning face'), (5, None)]

Keep the rows whose character is €:

run(f"SELECT id, name FROM {TABLE} WHERE chr(code) = '€' ORDER BY id LIMIT 10")

[(3, 'EURO SIGN')]

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

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

Because chr() runs on every row the scan sends, one row with an invalid code point fails the whole query, in SELECT and in WHERE alike. See Limitations and errors.

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

plan(f"SELECT id FROM {TABLE} WHERE chr(code) = '€'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│     (chr(code) = '€')     │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            code           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Compare the code, not the character. chr(code) = '€' keeps the same rows as code = 8364, but only the second is a comparison of a raw column with a constant, which 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 code = 8364")

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

• Combine it with a pushed-down condition. In id > 100 AND chr(code) = '€', id > 100 is still evaluated in VAST DataBase, so chr() runs only on the rows that pass it.

Limitations and errors

• Valid code points only. A negative number, a surrogate (55296 to 57343, U+D800 to U+DFFF) or a number above 1114111 fails with Invalid UTF8 Codepoint and the number. On a column, one such row fails the whole query.
• Guard a column that can hold invalid values. Return NULL for them with CASE, so that the query doesn't fail:

run(f"INSERT INTO {TABLE} VALUES (6, -1, 'NOT A CODE POINT')")
run(f"SELECT id, chr(code) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, CASE WHEN code BETWEEN 0 AND 1114111 AND code NOT BETWEEN 55296 AND 57343 "
    f"THEN chr(code) END FROM {TABLE} ORDER BY id LIMIT 10")

[(1,)]
OSError: Invalid UTF8 Codepoint -1
[(1, 'A'), (2, 'é'), (3, '€'), (4, '😀'), (5, None), (6, None)]

• INTEGER only. A decimal, such as 65.0, fails with No function matches the given name and argument types, and so does a BIGINT, such as a BIGINT column or chr(id + 64) on one. Cast it to INTEGER first: chr(CAST(65.0 AS INTEGER)). A string that isn't a number fails with Could not convert string 'abc' to INT32.
• One code point per call. To build a string of several code points, concatenate the results with || or concat.
• ord returns -1 for an empty string, and chr(-1) fails. Check for -1 before you pass the result of ord to chr.

run("SELECT chr(CAST(65.0 AS INTEGER)), chr(127467) || chr(127479)")

[('A', '🇫🇷')]

Similar functions

run("SELECT chr(233), ord('é'), unicode('é'), ascii('é')")
run("SELECT chr(ord('é')) = 'é'")

[('é', 233, 233, 233)]
[(True,)]

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 ascii.
• CASE: choosing a value.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.