--- title: "concat_ws" slug: "dev-database-reference-sql-functions-string-concat-ws" updated: 2026-10-30T18:18:56Z published: 2026-10-30T18:18:56Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-string-concat-ws" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # concat_ws *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Text basics](/develop/docs/dev-database-reference-sql-functions-string-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, ...) ``` | Argument | Type | Description | | --- | --- | --- | | `separator` | `VARCHAR` | The text to put between each pair of values. It can be empty (`''`), longer than one character, or a column. If it's NULL, the result is NULL. | | `value1`, `value2`, … | any type | The values to join: one or more. A value that isn't a `VARCHAR` is converted to text first. | **Returns:** `VARCHAR`. **Aliases:** none. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-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](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), 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, "") 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: | Call | Result | Why | | --- | --- | --- | | `concat_ws(',', 'Hello', 'x')` | `'Hello,x'` | The separator goes between the values, not at the ends. | | `concat_ws(', ', 'a', '', 'b')` | `'a, , b'` | An empty string is a value: it keeps its separator. | | `concat_ws(',', 'a', NULL, 'b')` | `'a,b'` | A NULL value is skipped, and so is its separator. | | `concat_ws('', 'a', 'b')` | `'ab'` | An empty separator joins the values directly. | | `concat_ws(',', 1, 2)` | `'1,2'` | Numbers are converted to text. | | `concat_ws(',', DATE '2026-10-16', 'x')` | `'2026-10-16,x'` | So are dates (`YYYY-MM-DD`)… | | `concat_ws(',', true, 'x')` | `'true,x'` | …booleans… | | `concat_ws(',', [1, 2], [3])` | `'[1, 2],[3]'` | …and lists, in brackets with `,` between the elements. | | `concat_ws('', ['a', 'b'], '-x')` | `'[a, b]-x'` | The text elements of a list are written without quotes. | | `concat_ws(',', '\xAA'::BLOB, 'x')` | `'\xAA,x'` | A `BLOB` becomes its escaped text form: `\xAA` is four characters. | 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 | Input | Result | | --- | --- | | A NULL separator: `concat_ws(NULL, 'a', 'b')` | `NULL` | | One NULL value among others: `concat_ws(',', NULL, 'x')` | `'x'` | | Every value NULL: `concat_ws(',', NULL)`, `concat_ws(',', NULL, NULL)` | `''` (an empty string, not NULL) | | An empty value: `concat_ws(',', '', 'x')` | `',x'` | | No values: `concat_ws(',')` | Error: `No function matches the given name and argument types` | | A separator that isn't a `VARCHAR`: `concat_ws(1, 'a', 'b')` | Error: `No function matches the given name and argument types` | The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-string-concat-ws#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](/develop/docs/dev-database-reference-sql-functions-lists-array-to-string); to join two lists, see [list_concat](/develop/docs/dev-database-reference-sql-functions-lists-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 | Function | NULL values | Result for `'a'`, `NULL`, `'b'` | | --- | --- | --- | | `concat_ws('-', …)` | skipped, with their separator | `'a-b'` | | [concat](/develop/docs/dev-database-reference-sql-functions-string-concat) | skipped (no separator) | `'ab'` | | [\|\| (concatenation)](/develop/docs/dev-database-reference-sql-functions-string-concat-operator) | any NULL makes the result NULL | `NULL` | | [array_to_string](/develop/docs/dev-database-reference-sql-functions-lists-array-to-string) | joins the elements of one list, with a separator | (lists only) | ``` 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](/develop/docs/dev-database-reference-sql-functions-string-text-basics): all the text functions. - [concat](/develop/docs/dev-database-reference-sql-functions-string-concat) and [|| (concatenation)](/develop/docs/dev-database-reference-sql-functions-string-concat-operator): joining without a separator. - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.