left

Applies to VAST Cluster 5.5.

Functions › Text basics › Parts of a string. Returns the first n characters of a string; with a negative n, everything except the last -n characters. There are no aliases.

Syntax

left(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. 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 products. Each SKU is a region, a country or state code and a five-digit serial number; 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"'

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}.products"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, sku VARCHAR, name VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'EU-FR-00042', 'Café crème'), "
    "(2, 'US-NY-00107', 'Bagel'), "
    "(3, 'JP-13-00300', 'Matcha latte'), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

left counts in Unicode code points from the start of the string:

A code point isn't always what a reader sees as one character. A letter followed by a combining accent (e and U+0301, displayed as é) is two code points, and left can split them: left('e\u0301x', 1) returns 'e' without its accent. A precomposed é (U+00E9) is one code point, so left('Café crème', 4) returns 'Café', accent included. To count what a reader sees, use left_grapheme.

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 left(NULL, 2), left('abc', NULL), left('', 2), left('abc', 0), left('abc', -1), left('abc', 10), "
    "left('e\u0301x', 1), left('abc', -9223372036854775807)")
run("SELECT left('abc', 9223372036854775807)")
run("SELECT left('abc', 2.7)")
run("SELECT left(123, 2)")

[(None, None, '', '', 'ab', 'abc', 'e', '')]
OSError: Substring length outside of supported range (> 4294967295)
InternalError: Binder Error: No function matches the given name and argument types 'left(STRING_LITERAL, DECIMAL(2,1))'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'left(INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The region (the first two characters of the SKU) and the first four characters of the name. Café keeps its é, because a precomposed é is one code point:

run(f"SELECT id, left(sku, 2), left(name, 4) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'EU', 'Café'), (2, 'US', 'Bage'), (3, 'JP', 'Matc'), (4, None, None)]

Drop the serial number and its dash, the last six characters, with a negative count:

run(f"SELECT id, left(sku, -6) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'EU-FR'), (2, 'US-NY'), (3, 'JP-13'), (4, None)]

Keep only the European products:

run(f"SELECT id, sku FROM {TABLE} WHERE left(sku, 2) = 'EU' ORDER BY id LIMIT 10")

[(1, 'EU-FR-00042')]

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

• In the SELECT list, the scan reads the column and the Query Engine computes left() 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() 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(sku, 2) = 'EU'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  ("left"(sku, 2) = 'EU')  │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            sku            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• A prefix test isn't pushed down either. sku LIKE 'EU%' is rewritten to prefix(sku, 'EU'), and prefix() runs in a FILTER step, like left(sku, 2) = 'EU'. Use whichever reads best; see starts_with for the prefix tests:

plan(f"SELECT id FROM {TABLE} WHERE sku LIKE 'EU%'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│     prefix(sku, 'EU')     │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            sku            │
│             id            │
│                           │
└───────────────────────────┘

• Store the prefix if you filter on it often. Compute it when you write the rows and keep it in its own column: region = 'EU' 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 sku = 'EU-FR-00042'")

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

• Combine it with a pushed-down condition. In id > 100 AND left(sku, 2) = 'EU', id > 100 is still evaluated in VAST DataBase, so left() 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(CAST(n AS VARCHAR), 2). For the first elements of a list, use list_slice.
• The count must be an integer. A DECIMAL such as 2.7 fails with No function matches the given name and argument types 'left(STRING_LITERAL, DECIMAL(2,1))'; it isn't converted for you. Cast it to an integer first: left(s, CAST(x AS BIGINT)). The cast rounds, it doesn't truncate: CAST(2.7 AS BIGINT) is 3, so left('abc', CAST(2.7 AS BIGINT)) returns 'abc'.
• A very large positive count fails. left('abc', 9223372036854775807) fails with Substring length outside of supported range (> 4294967295). A very large negative count returns an empty string.
• Code points, not characters. See What it does.

run("SELECT left(123, 2)")
run("SELECT left(CAST(123 AS VARCHAR), 2), left('abc', CAST(2.7 AS BIGINT))")

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

Similar functions

run("SELECT left('abcdef', 2), substring('abcdef', 1, 2), 'abcdef'[1:2], right('abcdef', 2)")
run("SELECT left('e\u0301x', 1), left_grapheme('e\u0301x', 1), substring('e\u0301x', 1, 1)")

[('ab', 'ab', 'ab', 'ef')]
[('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.
• right, substring and left_grapheme.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.