left_grapheme

Applies to VAST Cluster 5.5.

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

Syntax

left_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_left_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. The authors' names have combining accents and two posts start with a flag; 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_left_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, 'E\u0301mile', '\U0001F1EB\U0001F1F7 Paris in spring'), "
    "(2, 'Jose\u0301', 'Lunch at the cafe\u0301'), "
    "(3, 'Grace', '\U0001F1FA\U0001F1F8 New York'), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

left_grapheme works like left, but counts grapheme clusters instead of code points.

A code point is one Unicode value: the unit length, left and substring count. A grapheme cluster is what a reader sees as one character, and it can be several code points:

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 left_grapheme never returns half a character. The count follows the same rules as left:

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 third value shows that both code points of the é are kept:

run("SELECT left_grapheme(NULL, 1), left_grapheme('abc', NULL), left_grapheme('', 1), "
    "left_grapheme('abc', 0), left_grapheme('abc', -1)")
run("SELECT left_grapheme('e\u0301x', 1), length(left_grapheme('e\u0301x', 1)), "
    "left_grapheme('\U0001F1EB\U0001F1F7 Paris', 1)")
run("SELECT left_grapheme(123, 1)")

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

Examples

The first character of each author's name and of each post, counted both ways. left cuts the accent off É and returns half of each flag; left_grapheme keeps them whole:

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

[(1, 'E', 'É', '🇫', '🇫🇷'), (2, 'J', 'J', 'L', 'L'), (3, 'G', 'G', '🇺', '🇺🇸'), (4, None, None, None, None)]

Drop the last word of the first post and the space before it, seven grapheme clusters, with a negative count. The flag counts as one:

run(f"SELECT id, left_grapheme(body, -7) FROM {TABLE} WHERE id = 1")

[(1, '🇫🇷 Paris in')]

Keep only the posts that start with the French flag:

run(f"SELECT id FROM {TABLE} WHERE left_grapheme(body, 1) = '\U0001F1EB\U0001F1F7' 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 left_grapheme() is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes left_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 left_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 left_grapheme(body, 1) = '\U0001F1EB\U0001F1F7'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (left_grapheme(body, 1) = │
│            '🇫🇷')           │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            body           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Use left 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 left gives the same result.
• Store what you filter on. If you often filter on the first character, such as a flag that marks the country, keep it in its own column when you write the rows: flag = '🇫🇷' is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan for a raw column compared with a constant 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 left_grapheme(body, 1) = '🇫🇷', id > 100 is still evaluated in VAST DataBase, so left_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: left_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 'left_grapheme(STRING_LITERAL, DECIMAL(2,1))'; it isn't converted for you. Cast it to an integer first: left_grapheme(s, CAST(x AS BIGINT)). The cast rounds, it doesn't truncate.
• Grapheme clusters, not normalised text. left_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 left_grapheme(123, 1)")
run("SELECT left_grapheme(CAST(123 AS VARCHAR), 1)")

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

Similar functions

run("SELECT left_grapheme('e\u0301x', 1), left('e\u0301x', 1), substring_grapheme('e\u0301x', 1, 1), "
    "substring('e\u0301x', 1, 1)")

[('é', 'e', 'é', 'e')]

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