substring_grapheme

Applies to VAST Cluster 5.5.

Functions › Text basics › Parts of a string. Returns part of a string, counting positions and length in characters as a reader sees them (grapheme clusters): length of them from position start, or everything from start to the end. There are no aliases.

Syntax

substring_grapheme(s, start)
substring_grapheme(s, start, length)

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_substring_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. Two posts start with a flag, one ends in a combining accent; 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_substring_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

substring_grapheme works like substring, but counts grapheme clusters instead of code points.

A code point is one Unicode value: the unit length and substring count. A grapheme cluster is what a reader sees as one character, and it can be several code points: a letter followed by a combining accent (e and U+0301, displayed as é) is one grapheme cluster of two code points, and so is a flag emoji such as 🇫🇷. 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).

Counting grapheme clusters keeps positions where a reader expects them, and never returns half a character:

Both columns, as the cluster returns them, with the number of code points in some of the results. They use the table and helpers from Set up the examples:

run("SELECT substring_grapheme('cafe\u0301', 3, 2), substring('cafe\u0301', 3, 2), "
    "substring_grapheme('\U0001F1EB\U0001F1F7\U0001F1E9\U0001F1EA', 2, 1), "
    "length(substring('\U0001F1EB\U0001F1F7\U0001F1E9\U0001F1EA', 2, 1))")
run("SELECT substring_grapheme('e\u0301 \U0001F1EB\U0001F1F7 \u1E9E', 2, 2), "
    "length(substring('e\u0301 \U0001F1EB\U0001F1F7 \u1E9E', 2, 3))")

[('fé', 'fe', '🇩🇪', 1)]
[(' 🇫🇷', 3)]

The positions follow the same rules as substring:

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 substring_grapheme(NULL, 2, 2), substring_grapheme('abc', NULL, 1), substring_grapheme('', 2, 2), "
    "substring_grapheme('abc', 2), substring_grapheme('abc', 0, 2), substring_grapheme('abc', -1, 1)")
run("SELECT substring_grapheme('cafe\u0301', 3, 2), length(substring_grapheme('cafe\u0301', 3, 2)), "
    "substring_grapheme('\U0001F1EB\U0001F1F7\U0001F1E9\U0001F1EA', 2, 1)")
run("SELECT substring_grapheme('abc', 1, NULL), substring_grapheme('abc', 2, 0), substring_grapheme('abc', 2, -1), "
    "substring_grapheme('abc', 10)")
run("SELECT substring_grapheme(123, 2, 2)")

[(None, None, '', 'bc', 'a', 'c')]
[('fé', 3, '🇩🇪')]
[(None, '', 'a', '')]
InternalError: Binder Error: No function matches the given name and argument types 'substring_grapheme(INTEGER_LITERAL, INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

Each post without its first two characters, counted both ways. The flag is one grapheme cluster but two code points, so substring from position 3 keeps the space after it:

run(f"SELECT id, substring_grapheme(body, 3), substring(body, 3) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'Paris in spring', ' Paris in spring'), (2, 'nch at the café', 'nch at the café'), (3, 'New York', ' New York'), (4, None, None)]

The last four characters of each post, with a negative start. For café, substring counts the accent as a character and returns afé:

run(f"SELECT id, substring_grapheme(body, -4), substring(body, -4) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'ring', 'ring'), (2, 'café', 'afé'), (3, 'York', 'York'), (4, None, None)]

Find the posts whose third to seventh grapheme clusters are Paris:

run(f"SELECT id FROM {TABLE} WHERE substring_grapheme(body, 3, 5) = 'Paris' 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 substring_grapheme() is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes substring_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 substring_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 substring_grapheme(body, 3, 5) = 'Paris'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (substring_grapheme(body, │
│      3, 5) = 'Paris')     │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            body           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Use substring 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 substring gives the same result.
• Store the part you filter on. If you often filter on one part of a value, 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 substring_grapheme(body, 3, 5) = 'Paris', id > 100 is still evaluated in VAST DataBase, so substring_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: substring_grapheme(CAST(n AS VARCHAR), 2, 2).
• Positions and lengths must be integers. A DECIMAL such as 1.5 fails with No function matches the given name and argument types 'substring_grapheme(STRING_LITERAL, DECIMAL(2,1))'; it isn't converted for you. Cast it to an integer first: substring_grapheme(s, CAST(x AS BIGINT)). The cast rounds, it doesn't truncate.
• No FROM … FOR form and no slices. substring_grapheme('abc' FROM 2 FOR 1) fails with Parser Error: syntax error at or near "FROM". Pass the start and length as arguments: substring_grapheme('abc', 2, 1). The slice forms s[start:end] and s[i] count code points, like substring.
• Grapheme clusters, not normalised text. substring_grapheme keeps the code points it finds: a decomposed é 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 substring_grapheme(123, 2, 2)")
run("SELECT substring_grapheme(CAST(12345 AS VARCHAR), 2, 2)")

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

Similar functions

run("SELECT substring_grapheme('e\u0301x', 1, 1), substring('e\u0301x', 1, 1), left_grapheme('e\u0301x', 1), "
    "left('e\u0301x', 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.
• substring, left_grapheme, right_grapheme and length_grapheme.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.