right_grapheme

Applies to VAST Cluster 5.5.

Functions › Text basics › Parts of a string. Returns the last n characters of a string as a reader sees them (grapheme clusters); with a negative n, everything except the first -n. There are no aliases.

Syntax

right_grapheme(s, n)

Returns: VARCHAR.

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_right_grapheme. 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 posts. One author's name ends in a combining accent, and the posts end with a flag, a combining accent and a plain letter; 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_right_grapheme"'

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}.posts"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, author VARCHAR, body VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'Grace', 'Greetings from New York \U0001F1FA\U0001F1F8'), "
    "(2, 'Jose\u0301', 'Lunch at the cafe\u0301'), "
    "(3, 'Ada', 'Hello from London'), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

right_grapheme works like right, but counts grapheme clusters instead of code points.

A code point is one Unicode value: the unit length, right and substring count. A grapheme cluster is what a reader sees as one character, and it can be several code points. A combining accent comes after its letter, so the end of a string is where right cuts it off:

An emoji made of several emoji joined by zero-width joiners, such as a family, is also one grapheme cluster (length_grapheme counts it as 1). So right_grapheme never returns half a character. The count follows the same rules as right:

NULLs and edge cases

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples. length() of the result shows that both code points of the é are kept:

run("SELECT right_grapheme(NULL, 1), right_grapheme('abc', NULL), right_grapheme('', 1), "
    "right_grapheme('abc', -1), right_grapheme('abc', 10), right_grapheme('abc', 0)")
run("SELECT right_grapheme('cafe\u0301', 1), length(right_grapheme('cafe\u0301', 1))")
run("SELECT right_grapheme(123, 1)")

[(None, None, '', 'bc', 'abc', '')]
[('é', 2)]
InternalError: Binder Error: No function matches the given name and argument types 'right_grapheme(INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The last character of each author's name and of each post, counted both ways. right returns the accent of José and café without its letter, and half of the flag; right_grapheme keeps them whole:

run(f"SELECT id, right(author, 1), right_grapheme(author, 1), right(body, 1), right_grapheme(body, 1) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'e', 'e', '🇸', '🇺🇸'), (2, '́', 'é', '́', 'é'), (3, 'a', 'a', 'n', 'n'), (4, None, None, None, None)]

Drop the first word of each post and the space after it with a negative count. Greetings and Lunch have ten and six grapheme clusters:

run(f"SELECT id, right_grapheme(body, -10) FROM {TABLE} WHERE id = 1")
run(f"SELECT id, right_grapheme(body, -6) FROM {TABLE} WHERE id = 2")

[(1, 'from New York 🇺🇸')]
[(2, 'at the café')]

Keep only the posts that end with the US flag:

run(f"SELECT id FROM {TABLE} WHERE right_grapheme(body, 1) = '\U0001F1FA\U0001F1F8' ORDER BY id LIMIT 10")

[(1,)]

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

• In the SELECT list, the scan reads the column and the Query Engine computes right_grapheme() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes right_grapheme() 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 right_grapheme(body, 1) = '\U0001F1FA\U0001F1F8'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (right_grapheme(body, 1) =│
│            '🇺🇸')           │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            body           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Use right when the text is ASCII. For text with no accents, emoji or other multi-code-point characters, such as codes and identifiers, a code point is a grapheme cluster, and right gives the same result.
• Store what you filter on. If you often filter on the last character, keep it in its own column when you write the rows: a comparison of a raw column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan has no FILTER step:

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

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

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

Limitations and errors

• VARCHAR only. A number, a date, a boolean, a BLOB or a list fails with No function matches the given name and argument types. Cast a value to text first: right_grapheme(CAST(n AS VARCHAR), 1).
• The count must be an integer. A DECIMAL such as 2.7 fails with No function matches the given name and argument types 'right_grapheme(STRING_LITERAL, DECIMAL(2,1))'; it isn't converted for you. Cast it to an integer first: right_grapheme(s, CAST(x AS BIGINT)). The cast rounds, it doesn't truncate.
• Grapheme clusters, not normalised text. right_grapheme keeps the code points it finds: a decomposed é (e and U+0301) comes back as two code points, as the length() in NULLs and edge cases shows. nfc_normalize turns it into the one-code-point é.

run("SELECT right_grapheme(123, 1)")
run("SELECT right_grapheme(CAST(123 AS VARCHAR), 1)")

InternalError: Binder Error: No function matches the given name and argument types 'right_grapheme(INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.
[('3',)]

Similar functions

length() of each result shows how many code points it kept:

run("SELECT right_grapheme('cafe\u0301', 1), right('cafe\u0301', 1), substring_grapheme('cafe\u0301', -1), "
    "substring('cafe\u0301', -1)")
run("SELECT length(right_grapheme('cafe\u0301', 1)), length(right('cafe\u0301', 1)), "
    "length(substring_grapheme('cafe\u0301', -1)), length(substring('cafe\u0301', -1))")

[('é', '́', 'é', '́')]
[(2, 1, 2, 1)]

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.
• right, left_grapheme, substring_grapheme and length_grapheme.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.