length_grapheme

Applies to VAST Cluster 5.5.

Functions › Text basics › Length. Returns the number of grapheme clusters in a string: the characters a reader sees, so a letter with a combining accent or an emoji made of several code points counts as one. It has no aliases.

Syntax

length_grapheme(s)

Returns: BIGINT.

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_length_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 five short posts. Post 2 has an accent written as a combining mark, post 3 a flag and post 4 a family emoji. 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_length_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, 'ada', 'Hello'), "
    "(2, 'jose', 'Cafe\u0301 au lait'), "
    "(3, 'marie', 'Bonjour 🇫🇷'), "
    "(4, 'kim', '\U0001F468\u200D\U0001F469\u200D\U0001F467 at home'), "
    "(5, NULL, NULL)")

[]
[]
[(5,)]

What it does

A string is a sequence of Unicode code points, and length counts those. But some of what a reader sees as one character is made of several code points:

• A letter and combining marks. é can be one code point (U+00E9) or two: e followed by the combining acute accent U+0301. Both look the same.
• Flags. A flag emoji is two regional-indicator code points: 🇫🇷 is the indicators for F and R.
• Emoji sequences. 👨‍👩‍👧 is three emoji joined by two zero-width joiners (U+200D): five code points.

Unicode calls each of these visible units a grapheme cluster. length_grapheme counts grapheme clusters:

It works on long strings too: 100,000 copies of e followed by U+0301 count as 100,000.

Use it when a limit or a layout is about what people see, such as a display width or a character limit on a message. For storage limits, count bytes with strlen.

A carriage return followed by a line feed ('\r\n') counts as two.

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 length_grapheme(NULL), length_grapheme(''), length_grapheme('e\u0301'), length_grapheme('🇫🇷')")
run("SELECT length_grapheme('\U0001F468\u200D\U0001F469\u200D\U0001F467'), length_grapheme('\r\n'), length('\r\n')")
run("SELECT length('\U0001F468\u200D\U0001F469\u200D\U0001F467'), length('e\u0301 🇫🇷 ẞ'), length_grapheme('e\u0301 🇫🇷 ẞ')")
run("SELECT length_grapheme('\\xAA'::BLOB)")
run("SELECT length_grapheme(['a', 'b'])")
run("SELECT length_grapheme(123)")

[(None, 0, 1, 1)]
[(1, 2, 2)]
[(5, 7, 5)]
InternalError: Binder Error: No function matches the given name and argument types 'length_grapheme(BLOB)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'length_grapheme(VARCHAR[])'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'length_grapheme(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

Code points and grapheme clusters for each post. They differ for posts 2, 3 and 4:

run(f"SELECT id, length(body), length_grapheme(body) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 5, 5), (2, 13, 12), (3, 10, 9), (4, 13, 9), (5, None, None)]

Find the posts with combining marks, flags or emoji sequences: the ones where the two counts differ:

run(f"SELECT id, author FROM {TABLE} WHERE length(body) <> length_grapheme(body) ORDER BY id LIMIT 10")

[(2, 'jose'), (3, 'marie'), (4, 'kim')]

Apply a limit of 9 visible characters. Posts 3 and 4 fit: each has 9 grapheme clusters, but 10 and 13 code points, so the same limit on length would reject them:

run(f"SELECT id, author FROM {TABLE} WHERE length_grapheme(body) <= 9 ORDER BY id LIMIT 10")

[(1, 'ada'), (3, 'marie'), (4, 'kim')]

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

• In the SELECT list, the scan reads the column and the Query Engine computes length_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 length_grapheme() and keeps the rows that match. No rows are skipped in storage.

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

plan(f"SELECT id FROM {TABLE} WHERE length_grapheme(body) <= 9")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (length_grapheme(body) <= │
│             9)            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            body           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Test for empty strings with = '', not length_grapheme(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 body = '' has no FILTER step:

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

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

• Store the count if you filter on it often. Compute it when you write the rows and keep it in its own column: body_chars <= 9 is pushed down, length_grapheme(body) <= 9 isn't.
• Combine it with a pushed-down condition. In id > 2 AND length_grapheme(body) <= 9, id > 2 is still evaluated in VAST DataBase, so length_grapheme() runs only on the rows that pass it.

Limitations and errors

• Strings only. A number, a date, a boolean, a list or a BLOB fails with No function matches the given name and argument types; the error lists length_grapheme(VARCHAR) as the only form. For the elements of a list, use length.
• \r\n is two. A carriage return and line feed pair counts as two grapheme clusters. If line endings matter to a limit, replace '\r\n' with '\n' first.

run("SELECT length_grapheme([1, 2])")
run("SELECT length_grapheme(replace('a\r\nb', '\r\n', '\n'))")

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

Similar functions

run("SELECT length_grapheme('e\u0301x'), length('e\u0301x'), strlen('e\u0301x')")
run("SELECT left_grapheme('e\u0301x', 1), left('e\u0301x', 1), substring_grapheme('e\u0301x', 1, 1), right_grapheme('e\u0301x', 1)")

[(2, 3, 4)]
[('é', 'e', 'é', 'x')]

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.
• length and strlen: code points and bytes.
• left_grapheme, right_grapheme and substring_grapheme: cut a string without splitting what a reader sees as a character.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.