--- title: "concat" slug: "dev-database-reference-sql-functions-string-concat" updated: 2026-10-30T18:18:56Z published: 2026-10-30T18:18:56Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-string-concat" --- > ## 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 *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 its arguments into one string, converting numbers, dates, booleans and other values to text and skipping NULLs. Given only lists, it joins the lists. It has no aliases; the [concatenation operator](/develop/docs/dev-database-reference-sql-functions-string-concat-operator) `||` is similar but returns NULL if any part is NULL. ## Syntax ``` concat(a [, b, ...]) ``` | Argument | Type | Description | | --- | --- | --- | | `a`, `b`, ... | any | One or more values to join. Values that aren't strings are converted to text. Either all strings and other scalar values, or all lists. | **Returns:** `VARCHAR`, or a list when the arguments are lists. **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`. 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. Row 3 has no city, 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"' 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, last_name VARCHAR, city VARCHAR, born BIGINT)") run(f"INSERT INTO {TABLE} VALUES " "(1, 'Ada', 'Lovelace', 'London', 1815), " "(2, 'Grace', 'Hopper', 'New York', 1906), " "(3, 'José', 'Martí', NULL, 1853), " "(4, NULL, NULL, NULL, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does `concat` joins its arguments in order, with nothing between them. Each argument that isn't a `VARCHAR` is first converted to its text form, and NULL arguments are skipped: | Arguments | Example | Result | | --- | --- | --- | | Strings | `concat('Ada', ' ', 'Lovelace')` | `'Ada Lovelace'` | | Numbers | `concat(1, 2)` | `'12'` | | Mixed scalar types | `concat('a', 1, true, DATE '2026-10-16', 2.5)` | `'a1true2026-10-162.5'` | | A `BLOB` | `concat('\xAA'::BLOB, '-', 'x')` | `'\xAA-x'`: the `BLOB`'s text form, where a byte that isn't a printable character is written as `\x` and two hex digits | | A NULL | `concat('a', NULL)` | `'a'` | | Only NULLs | `concat(NULL, NULL)` | `''`, a `VARCHAR` | | Lists | `concat([1], [2])` | `[1, 2]`, a list | | A NULL and a list | `concat(NULL, [1])`, `concat([1], NULL)` | `[1]`: the NULL is skipped | Because NULLs are skipped, `concat` never returns NULL for string arguments: a row whose values are all NULL gives `''`, or just the separators you passed. To put a separator between the values, and skip it next to NULLs, use [concat_ws](/develop/docs/dev-database-reference-sql-functions-string-concat-ws). ## NULLs and edge cases | Input | Result | | --- | --- | | `concat(NULL, '-', 'x')` | `'-x'` | | `concat('a', NULL)` | `'a'` | | `concat(NULL, NULL)` | `''` | | `concat('a')` (one argument) | `'a'` | | `concat()` (no arguments) | Error: `No function matches the given name and argument types 'concat()'` | | `typeof(concat(NULL, NULL))` | `'VARCHAR'` | | `concat(1, 2)` | `'12'` | | `concat('a', 1, true, DATE '2026-10-16', 2.5)` | `'a1true2026-10-162.5'` | | `concat('\xAA'::BLOB, '-', 'x')` | `'\xAA-x'` (Python shows the backslash doubled) | | `concat([1], [2])` | `[1, 2]` | | `concat(NULL, [1])` | `[1]` | | `concat('a', [1, 2])` | Error: `Cannot concatenate types VARCHAR and INTEGER[] - an explicit cast is required` | | `concat([1], 'a')` | Error: `Cannot concatenate types INTEGER[] and VARCHAR - an explicit cast is required` | 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#set-up-the-examples): ``` run("SELECT concat(NULL, '-', 'x'), concat('a', NULL), concat(NULL, NULL), typeof(concat(NULL, NULL)), concat('a')") run("SELECT concat(1, 2), concat('a', 1, true, DATE '2026-10-16', 2.5), concat('\\xAA'::BLOB, '-', 'x')") run("SELECT concat([1], [2]), concat(NULL, [1])") run("SELECT concat()") run("SELECT concat('a', [1, 2])") run("SELECT concat([1], 'a')") ``` ``` [('-x', 'a', '', 'VARCHAR', 'a')] [('12', 'a1true2026-10-162.5', '\\xAA-x')] [([1, 2], [1])] InternalError: Binder Error: No function matches the given name and argument types 'concat()'. You might need to add explicit type casts. InternalError: Binder Error: Cannot concatenate types VARCHAR and INTEGER[] - an explicit cast is required InternalError: Binder Error: Cannot concatenate types INTEGER[] and VARCHAR - an explicit cast is required ``` ## Examples A display name with the city. In row 3 the missing city is skipped, so the parentheses are empty; in row 4 only the separators are left: ``` run(f"SELECT id, concat(first_name, ' ', last_name, ' (', city, ')') FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, 'Ada Lovelace (London)'), (2, 'Grace Hopper (New York)'), (3, 'José Martí ()'), (4, ' ()')] ``` Numbers are converted to text, so you can build a label without a cast: ``` run(f"SELECT id, concat('user-', id, ' (born ', born, ')') FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, 'user-1 (born 1815)'), (2, 'user-2 (born 1906)'), (3, 'user-3 (born 1853)'), (4, 'user-4 (born )')] ``` Find a person by full name: ``` run(f"SELECT id FROM {TABLE} WHERE concat(first_name, ' ', last_name) = 'Ada Lovelace' ORDER BY id LIMIT 10") ``` ``` [(1,)] ``` ## 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()` is always computed by the Query Engine: - **In the `SELECT` list,** the scan reads the columns and the Query Engine computes `concat()` 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()` and keeps the rows that match. No rows are skipped in storage. The plan of the full-name example shows the `FILTER` step above the scan: ``` plan(f"SELECT id FROM {TABLE} WHERE concat(first_name, ' ', last_name) = 'Ada Lovelace'") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #2 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (concat(first_name, ' ', │ │ last_name) = 'Ada │ │ Lovelace') │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ first_name │ │ last_name │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Compare the parts, not the joined string.** `first_name = 'Ada' AND last_name = 'Lovelace'` finds the same row as the full-name example, but each part is a comparison of a raw column with a constant, which is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan has no `FILTER` step: ``` run(f"SELECT id FROM {TABLE} WHERE first_name = 'Ada' AND last_name = 'Lovelace' ORDER BY id LIMIT 10") plan(f"SELECT id FROM {TABLE} WHERE first_name = 'Ada' AND last_name = 'Lovelace'") ``` ``` [(1,)] ┌───────────────────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ``` - **Join in the `SELECT` list, after filtering.** `concat()` in the `SELECT` list runs only on the rows that pass the `WHERE` conditions, so filter on raw columns first and build the string for the rows you return. - **Store the joined value if you filter on it often.** Compute it when you write the rows and keep it in its own column: `full_name = 'Ada Lovelace'` is pushed down, `concat(...) = 'Ada Lovelace'` isn't. ## Limitations and errors - **Lists and other values can't be mixed.** A list with a string, such as `concat(['a', 'b'], '-', 'x')`, fails with `Cannot concatenate types VARCHAR[], VARCHAR and VARCHAR - an explicit cast is required`. To put a list's text form into a string, use [concat_ws](/develop/docs/dev-database-reference-sql-functions-string-concat-ws), which converts lists to text. - **Lists must have the same element type.** `concat([1], ['a'])` fails with `Cannot concatenate lists of types INTEGER[] and VARCHAR[] - an explicit cast is required`. - **At least one argument.** `concat()` fails with `No function matches the given name and argument types 'concat()'`. - **`BLOB` values become text.** `concat` with a `BLOB` returns a `VARCHAR` with the `BLOB`'s text form, not bytes, even when every argument is a `BLOB`: `concat('\xAA'::BLOB, '\xBB'::BLOB)` is the `VARCHAR` `'\xAA\xBB'`. To join `BLOB` values as bytes, use the [concatenation operator](/develop/docs/dev-database-reference-sql-functions-string-concat-operator) on two `BLOB` values. ``` run("SELECT concat(['a', 'b'], '-', 'x')") run("SELECT concat_ws('-', ['a', 'b'], 'x')") ``` ``` InternalError: Binder Error: Cannot concatenate types VARCHAR[], VARCHAR and VARCHAR - an explicit cast is required [('[a, b]-x',)] ``` ## Similar functions | Function | NULL handling | Result for `'a'`, `NULL`, `'b'` (`concat` gives `'ab'`) | Result for `[1, 2]` and `[3]` (`concat` gives `[1, 2, 3]`) | | --- | --- | --- | --- | | [concatenation operator](/develop/docs/dev-database-reference-sql-functions-string-concat-operator) `\|\|` | returns NULL if any part is NULL | `'a' \|\| NULL \|\| 'b'` is `NULL` | `[1, 2] \|\| [3]` is `[1, 2, 3]` | | [concat_ws](/develop/docs/dev-database-reference-sql-functions-string-concat-ws) | skips NULLs and the separator next to them | `concat_ws('-', 'a', NULL, 'b')` is `'a-b'` | `concat_ws(',', [1, 2], [3])` is `'[1, 2],[3]'`, a string | | [list_concat](/develop/docs/dev-database-reference-sql-functions-lists-list-concat) | treats a NULL list as empty | (lists only) | `[1, 2, 3]` | ``` run("SELECT concat('a', NULL, 'b'), 'a' || NULL || 'b', concat_ws('-', 'a', NULL, 'b')") run("SELECT concat([1, 2], [3]), [1, 2] || [3], concat_ws(',', [1, 2], [3]), list_concat([1, 2], [3])") ``` ``` [('ab', None, 'a-b')] [([1, 2, 3], [1, 2, 3], '[1, 2],[3]', [1, 2, 3])] ``` 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. - [The concatenation operator](/develop/docs/dev-database-reference-sql-functions-string-concat-operator) and [concat_ws](/develop/docs/dev-database-reference-sql-functions-string-concat-ws). - [list_concat](/develop/docs/dev-database-reference-sql-functions-lists-list-concat): join lists. - [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.