concat_ws

Applies to VAST Cluster 5.5.

Functions › Text basics › Concatenation. Joins one or more values into one string, with a separator between each pair. NULL values are skipped, with their separator. There are no aliases.

Syntax

concat_ws(separator, value1, value2, ...)

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_concat_ws. 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 people. Ada Lovelace has no middle name, José Martí no country, 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_concat_ws"'

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}.people"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, first_name VARCHAR, middle_name VARCHAR, "
    "last_name VARCHAR, city VARCHAR, country VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'Ada', NULL, 'Lovelace', 'London', 'UK'), "
    "(2, 'Grace', 'Brewster', 'Hopper', 'New York', 'USA'), "
    "(3, 'José', 'Julián', 'Martí', 'Havana', NULL), "
    "(4, NULL, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

concat_ws ("concatenate with separator") converts each value to text and joins them in order, with separator between each pair:

Skipping NULL values is what sets concat_ws apart: a list of optional parts, such as a middle name or a county in an address, joins without doubled or dangling separators.

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 concat_ws(NULL, 'a', 'b'), concat_ws(',', NULL, 'x'), concat_ws(',', NULL), "
    "concat_ws(',', NULL, NULL), concat_ws(',', '', 'x')")
run("SELECT concat_ws(',')")
run("SELECT concat_ws(1, 'a', 'b')")

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

Examples

Each person's full name, and where they live. The missing middle name and the missing country are skipped with their separators, and the row with no values gives empty strings:

run(f"SELECT id, concat_ws(' ', first_name, middle_name, last_name), concat_ws(', ', city, country) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'Ada Lovelace', 'London, UK'), (2, 'Grace Brewster Hopper', 'New York, USA'), (3, 'José Julián Martí', 'Havana'), (4, '', '')]

A semicolon-separated line per person. The BIGINT id is converted to text:

run(f"SELECT concat_ws(';', id, last_name, city) FROM {TABLE} ORDER BY id LIMIT 10")

[('1;Lovelace;London',), ('2;Hopper;New York',), ('3;Martí;Havana',), ('4',)]

Find a person by the name you'd display:

run(f"SELECT id FROM {TABLE} WHERE concat_ws(' ', first_name, last_name) = 'Grace Hopper' ORDER BY id LIMIT 10")

[(2,)]

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

• In the SELECT list, the scan reads the columns and the Query Engine computes concat_ws() for each row it returns.
• In WHERE, the scan sends the columns for every row, and a FILTER step in the Query Engine computes concat_ws() 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 concat_ws(' ', first_name, last_name) = 'Grace Hopper'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (concat_ws(' ', first_name│
│   , last_name) = 'Grace   │
│          Hopper')         │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│         first_name        │
│         last_name         │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Compare the columns, not the joined string. To find one person, test each column against its own constant: first_name = 'Grace' AND last_name = 'Hopper'. 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 first_name = 'Grace' AND last_name = 'Hopper'")

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

• Store the joined value if you filter on it often. Build it with concat_ws when you write the rows and keep it in its own column: full_name = 'Grace Hopper' is pushed down, concat_ws(' ', first_name, last_name) = 'Grace Hopper' isn't.
• Combine it with a pushed-down condition. In id > 100 AND concat_ws(' ', first_name, last_name) = 'Grace Hopper', id > 100 is still evaluated in VAST DataBase, so concat_ws() runs only on the rows that pass it.

Limitations and errors

• At least one value. concat_ws(','), with a separator and nothing to join, fails with No function matches the given name and argument types 'concat_ws(STRING_LITERAL)'.
• The separator must be text. concat_ws(1, 'a', 'b') fails with No function matches the given name and argument types. Write the separator as a string: concat_ws('1', 'a', 'b').
• The result is always text. Lists are joined as their text form ('[1, 2],[3]'), not into one list. To join the elements of one list into a string, see array_to_string; to join two lists, see list_concat.
• Empty strings aren't skipped. Only NULL values are. To skip empty strings too, turn them into NULL first with NULLIF(value, '').

run("SELECT concat_ws(1, 'a', 'b')")
run("SELECT concat_ws('1', 'a', 'b'), concat_ws(', ', 'a', NULLIF('', ''), 'b')")

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

Similar functions

run("SELECT concat_ws('-', 'a', NULL, 'b'), concat('a', NULL, 'b'), 'a' || NULL || 'b'")

[('a-b', 'ab', None)]

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.
• concat and || (concatenation): joining without a separator.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.