string_split

Applies to VAST Cluster 5.5.

Functions › Search and split › Splitting. Splits a string at every occurrence of a separator and returns the parts as a list (VARCHAR[]). The separator is literal text, not a pattern. str_split, split and string_to_array are the same function.

Syntax

string_split(s, separator)
str_split(s, separator)
split(s, separator)
string_to_array(s, separator)

Returns: VARCHAR[], a list that the driver returns as a Python list.

Aliases: str_split, split, string_to_array.

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_string_string_split. 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 servers, each with a comma-separated list of tags and a file path. Row 2 has an empty tag between two commas, row 3 has no tags, and 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_string_string_split"'

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}.servers"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, tags VARCHAR, path VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'prod,web', '/data/logs/app.log'), "
    "(2, 'prod,db,,eu', '/data/db/main.db'), "
    "(3, '', '/tmp/x'), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

string_split(s, separator) cuts the string at every occurrence of the separator, from left to right, and returns the pieces in order. Nothing is dropped:

An empty separator splits the string into code points, not into what a reader sees as characters: a flag emoji becomes its two halves, and e followed by a combining accent becomes e and the accent alone.

The result is a list, so the list functions work on it: [1] takes the first part, [-1] the last, [2:3] the second and third, and len() counts the parts. A position past the end gives NULL. To split and take one part in one step, use split_part.

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 string_split(NULL, ','), string_split('a,b', NULL), string_split('', ','), len(string_split('', ',')), "
    "string_split('a,b', ',,')")
run("SELECT string_split('a,b,,c', ','), string_split(',a,', ','), string_split('a b  c', ' '), "
    "string_split('a::b::c', '::'), string_split('a.b', '.'), string_split('aXbxc', 'x')")
run("SELECT string_split('abc', ''), string_split('\U0001F1EB\U0001F1F7', ''), string_split('e\u0301', '')")
run("SELECT string_split('a,b,c', ',')[1], string_split('a,b,c', ',')[-1], string_split('a,b', ',')[5], "
    "string_split('a,b,c', ',')[2:3], len(string_split('a,b,c', ','))")
run("SELECT string_split(123, ',')")
run("SELECT string_split('a,b,c', ',', 2)")

[(None, ['a,b'], [''], 1, ['a,b'])]
[(['a', 'b', '', 'c'], ['', 'a', ''], ['a', 'b', '', 'c'], ['a', 'b', 'c'], ['a', 'b'], ['aXb', 'c'])]
[(['a', 'b', 'c'], ['🇫', '🇷'], ['e', '́'])]
[('a', 'c', None, ['b', 'c'], 3)]
InternalError: Binder Error: No function matches the given name and argument types 'string_split(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'string_split(STRING_LITERAL, STRING_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The tags of each server as a list, how many there are, and the first one. Row 2 keeps its empty tag:

run(f"SELECT id, string_split(tags, ','), len(string_split(tags, ',')), string_split(tags, ',')[1] "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, ['prod', 'web'], 2, 'prod'), (2, ['prod', 'db', '', 'eu'], 4, 'prod'), (3, [''], 1, ''), (4, None, None, None)]

The four names are the same function. A path that starts with / gives an empty first part:

run(f"SELECT string_split(path, '/'), str_split(path, '/'), split(path, '/'), string_to_array(path, '/') "
    f"FROM {TABLE} WHERE id = 1 LIMIT 10")

[(['', 'data', 'logs', 'app.log'], ['', 'data', 'logs', 'app.log'], ['', 'data', 'logs', 'app.log'], ['', 'data', 'logs', 'app.log'])]

Keep the servers tagged db, and get one row for each tag with unnest. The other columns repeat on each row:

run(f"SELECT id, tags FROM {TABLE} WHERE list_contains(string_split(tags, ','), 'db') ORDER BY id LIMIT 10")
run(f"SELECT id, unnest(string_split(tags, ',')) AS tag FROM {TABLE} WHERE id = 1 LIMIT 10")

[(2, 'prod,db,,eu')]
[(1, 'prod'), (1, 'web')]

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

• In the SELECT list, the scan reads the column and the Query Engine splits it for each row it returns.
• In WHERE, for example inside list_contains(), the scan sends the column for every row, and a FILTER step in the Query Engine splits it and keeps the rows that match. No rows are skipped in storage.

The plan of the list_contains() example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE list_contains(string_split(tags, ','), 'db')")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ list_contains(string_split│
│     (tags, ','), 'db')    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            tags           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Filter on a prefix with a range. To find paths under /data/, path >= '/data/' AND path < '/data0' is pushed down to VAST DataBase, so rows that don't match never leave storage; string_split(path, '/')[2] = 'data' isn't. (0 is the character after /.) The plan of the range has no FILTER step.
• Store lists as lists. If you often search a delimited string, write it as a VARCHAR[] column, or as one row per value, when you load the data, so queries don't split it every time.
• split_part costs the same. split_part is planned as string_split() followed by taking one element, so choose whichever reads better; neither is pushed down.

plan(f"SELECT id FROM {TABLE} WHERE path >= '/data/' AND path < '/data0'")
run(f"SELECT id FROM {TABLE} WHERE path >= '/data/' AND path < '/data0' ORDER BY id LIMIT 10")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
[(1,), (2,)]

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 'string_split(INTEGER_LITERAL, STRING_LITERAL)' (with the type you passed). Cast it first: string_split(CAST(n AS VARCHAR), '0').
• No limit argument. string_split(s, sep, 2) fails with No function matches the given name and argument types 'string_split(STRING_LITERAL, STRING_LITERAL, INTEGER_LITERAL)'. Take the parts you need with a slice, such as [1:2].
• A literal separator only. To split on a pattern, such as any run of spaces or several separator characters, use regexp_split_to_array.
• No split_to_table. It fails with Scalar Function with name split_to_table does not exist!. Use unnest(string_split(s, sep)), as in Examples.

run("SELECT string_split(CAST(10203 AS VARCHAR), '0'), string_split('a,b,c', ',')[1:2]")
run("SELECT split_to_table('a,b', ',')")

[(['1', '2', '3'], ['a', 'b'])]
InternalError: Catalog Error: Scalar Function with name split_to_table does not exist!

Similar functions

The separator . is literal for string_split and a pattern for regexp_split_to_array:

run("SELECT string_split('a,b,,c', ','), split_part('a,b,,c', ',', 2), regexp_split_to_array('a,b,,c', ','), "
    "regexp_extract_all('a,b,,c', '[^,]+')")
run("SELECT string_split('a.b', '.'), regexp_split_to_array('a.b', '.'), regexp_split_to_array('a.b', '\\.')")

[(['a', 'b', '', 'c'], 'b', ['a', 'b', '', 'c'], ['a', 'b', 'c'])]
[(['a', 'b'], ['', '', '', ''], ['a', 'b'])]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Search and split: all the search and split functions.
• split_part, regexp_split_to_array and unnest.
• List functions, such as list_contains and array_to_string.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.