substring

Applies to VAST Cluster 5.5.

Functions › Text basics › Parts of a string. Returns part of a string: length characters from position start, or everything from start to the end. substr is the same function, SUBSTRING(s FROM start FOR length) is the standard SQL form of it, and s[start:end] and s[i] slice a string by position.

Syntax

substring(s, start)
substring(s, start, length)
substr(s, start)
substr(s, start, length)
SUBSTRING(s FROM start)
SUBSTRING(s FROM start FOR length)
s[start:end]
s[:end]
s[start:]
s[i]

Returns: VARCHAR.

Aliases: substr. SUBSTRING(s FROM start FOR length) is the same call as substring(s, start, length).

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. 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 (positions 1 and 2), a dash, a country or state code (positions 4 and 5), a dash and a five-digit serial number (positions 7 to 11); 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"'

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

substring counts in Unicode code points. Think of the characters as numbered from 1, with an empty position 0 before the first one. With a positive length, substring(s, start, length) takes the positions from start to start + length - 1, and returns the characters that exist in that range:

A start of 0 is the empty position before the first character, and it counts towards length: substring('abcdef', 0, 2) takes positions 0 and 1, so it returns one character, 'a'.

A negative start counts from the end: -1 is the last character, -2 the one before it. substring('abcdef', -2) returns 'ef', and substring('abcdef', -2, 1) returns 'e'.

A negative length takes characters before start, not after it: substring('abcdef', 2, -1) takes the one character before position 2 and returns 'a'. Use a positive length unless that's what you want.

Slices: s[start:end] and s[i]

The bracket forms take positions, not a length, and include both ends:

A negative bound counts from the end, as a negative start does. A position that doesn't exist gives an empty string, not an error. Slices count code points too: on 'e\u0301x', [1:1] returns 'e' without its accent and [2] returns the accent alone. Both forms return a VARCHAR.

Code points, not what a reader sees

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 so is a flag emoji. substring('e\u0301x', 1, 1) returns 'e' without its accent, and a range can start on the accent or end in the middle of a flag: substring('e\u0301 🇫🇷', 2, 3) returns the accent alone, a space and the first half of the flag. To count what a reader sees, use substring_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 substring(NULL, 2, 3), substring('abc', NULL), substring('abc', 1, NULL), substring('', 2, 3)")
run("SELECT substring('abcdef', 0, 2), substring('abcdef', -2), substring('abcdef', -2, 1), "
    "substring('abcdef', 2, -1), substring('abcdef', 2, 0), substring('abcdef', 10), substring('e\u0301x', 1, 1)")
run("SELECT substring('abcdef' FROM 2 FOR 3), substring('abcdef' FROM 3), substr('abcdef', 3, 2)")
run("SELECT 'abcdef'[2:3], 'abcdef'[:2], 'abcdef'[4:], 'abcdef'[2], 'abcdef'[-1]")
run("SELECT substring('e\u0301 \U0001F1EB\U0001F1F7', 2, 3), length(substring('e\u0301 \U0001F1EB\U0001F1F7', 2, 3))")
run("SELECT substring('abc', 1.5)")
run("SELECT substring(123, 2, 3)")

[(None, None, None, '')]
[('a', 'ef', 'e', 'a', '', '', 'e')]
[('bcd', 'cdef', 'cd')]
[('bc', 'ab', 'def', 'b', 'f')]
[('́ 🇫', 3)]
InternalError: Binder Error: No function matches the given name and argument types 'substring(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 'substring(INTEGER_LITERAL, INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The country or state code, three ways: with a start and a length, in the standard SQL form, and as a slice of positions 4 to 5:

run(f"SELECT id, substring(sku, 4, 2), SUBSTRING(sku FROM 4 FOR 2), sku[4:5] FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'FR', 'FR', 'FR'), (2, 'NY', 'NY', 'NY'), (3, '13', '13', '13'), (4, None, None, None)]

The serial number, from position 7 to the end with substr, and as the last five characters with a negative start:

run(f"SELECT id, substr(sku, 7), substring(sku, -5) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '00042', '00042'), (2, '00107', '00107'), (3, '00300', '00300'), (4, None, None)]

Keep only the products for France:

run(f"SELECT id, sku FROM {TABLE} WHERE substring(sku, 4, 2) = 'FR' 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 substring() and the slices are always computed by the Query Engine:

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

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

Make it faster

• Store the part you filter on. If you often filter on one part of a value, such as the country in a SKU, split it into its own column when you write the rows: country = 'FR' 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      │
│                           │
└───────────────────────────┘

• A prefix test isn't faster. substring(sku, 1, 2) = 'EU' and sku LIKE 'EU%' both run in a FILTER step: LIKE 'EU%' is rewritten to prefix(sku, 'EU'), which isn't pushed down either.
• Combine it with a pushed-down condition. In id > 100 AND substring(sku, 4, 2) = 'FR', id > 100 is still evaluated in VAST DataBase, so substring() 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(CAST(n AS VARCHAR), 2, 3). For part of a list, use list_slice.
• Positions and lengths must be integers. A DECIMAL such as 1.5 fails with No function matches the given name and argument types 'substring(STRING_LITERAL, DECIMAL(2,1))'; it isn't converted for you. Cast it to an integer first: substring(s, CAST(x AS BIGINT)). The cast rounds, it doesn't truncate: CAST(1.5 AS BIGINT) is 2, so substring('abc', CAST(1.5 AS BIGINT)) returns 'bc'.
• No mid or substring_index. Both fail with Scalar Function with name … does not exist!. Use substring(s, start, length) for mid, and split_part to take a part between separators.
• A negative length counts backwards. substring('abcdef', 2, -1) returns 'a', not an error or an empty string. See What it does.
• Code points, not characters. substring can split a letter from its accent, or a flag in two. Use substring_grapheme for text with accents or emoji.

run("SELECT substring(123, 2, 3)")
run("SELECT substring(CAST(12345 AS VARCHAR), 2, 3), substring('abc', CAST(1.5 AS BIGINT))")

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

Similar functions

On 'e\u0301x' (éx), substring and substring_grapheme differ:

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

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