Shape / columns · All operations

Combine columns into one

Concatenate several columns into one.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one row out per row in, with columns added or changed
Level
one clause of SQL

The DuckDB step

Concatenate columns

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
SELECT order_id, concat_ws(' / ', region, product, order_id) AS label FROM orders;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT max(label) FILTER (WHERE order_id = 1) = 'north / widget / 1' AND max(label) FILTER (WHERE order_id = 9) = 'gadget / 9' FROM result)) THEN 'as expected' ELSE error('the label did not join its parts and skip the missing region') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the label did not join its parts and skip the missing region”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Apache Hop (1)
  • Concat fieldsread 2026-09-27

    Concatenate several fields into one, with a separator and enclosure

AWS Glue (1)
  • Concatenate Columnsread 2026-09-27

    Build a string column from the values of other columns, with an optional spacer

Duckle (1)
  • Concatread 2026-09-25

    concatenate columns

Ibis (2)
  • StringConcatread 2026-09-27

    Concatenate several strings

  • StringJoinread 2026-09-27

    Join several strings with a separator

KNIME Analytics Platform (1)
  • Column Combinerread 2026-09-27

    Concatenate several columns into one delimited string column

OpenRefine (1)
  • Join columns…read 2026-09-27

    Concatenate chosen columns in a chosen order with a separator and a stand-in for nulls

Pentaho Data Integration (1)
  • Concat Fieldsread 2026-09-27

    Concatenate several fields into one with a separator and enclosure

Polars (2)
  • concat_strread 2026-09-27

    Join several columns into one string column, with a separator

  • formatread 2026-09-27

    Build a string column from a template with a placeholder per expression

Power Query M (2)
  • Combiner functionsread 2026-09-27

    5 functions returning text joiners: Combiner.CombineTextByDelimiter, …ByEachDelimiter, …ByLengths, …ByPositions, …ByRanges

  • Table.CombineColumnsread 2026-09-27

    Merge several columns into one new column with a combiner function

RapidMiner Studio (2)
Talend (2)
  • tWriteDelimitedFieldsread 2026-09-27

    Combine the columns of each row into one delimited string or byte array column

  • tWritePositionalFieldsread 2026-09-27

    Combine the columns of each row into one fixed-format string or byte array column

Trifacta (2)
  • Merge Transformread 2026-09-27

    Concatenate two or more columns into a new string column, with optional delimiter

  • Merge columnsread 2026-09-27

    Concatenate the values of two or more columns into a new column

VisiData (1)
  • join-colsread 2026-09-23

    Add column concatenating the selected source columns

Background

In a database, concatenation can join several fields of one record, such as a customer's street name, city and postal code, into a single address string.[1] In AWS Glue, the Concatenate transform builds a new string column from the values of other columns, with an optional spacer between them.[2] In formal language theory and computer programming, concatenation is the operation of joining sequential objects, such as character strings, end-to-end.[1]

When to use it

One principle of relational database design is that each field should reflect a single characteristic of the table's subject, so fields should not hold concatenated strings.[1] Entering the city, state, ZIP code and nation separately allows data-entry validation, and the separate items can be used for sorting or indexing the records.[1] Combine columns for a label, a display value, an export or a composite key, and keep the separate columns as the source the combined value is rebuilt from. (Meridian's view)

In SQL, string concatenation with a NULL operand generally results in NULL, so 'Fish ' || NULL || 'Chips' gives NULL, though not in every database.[3] Oracle treats NULL and the empty string as the same thing, so 'Fish ' || NULL || 'Chips' gives 'Fish Chips' there.[3] In DuckDB, the || operator returns NULL when any input is NULL, while the concat and concat_ws functions skip NULL inputs.[4] In Polars, concat_str propagates nulls by default, so a row with any null value gives a null result unless ignore_nulls is set.[5] OpenRefine's Join columns lets the user define a string to insert into empty cells, as well as an optional separator.[6] Before combining, decide whether a missing part should blank the whole value, be left out, or be shown by a stand-in, and choose the function that does exactly that. (Meridian's view)

Apache Hop's Concat fields transform can put an enclosure, a pair of strings, around fields, which allows separator characters inside the fields.[7] If the combined value may ever be split apart again, pick a separator that cannot occur in the values, or enclose each part. (Meridian's view)

The method

Concatenation joins its parts end-to-end, so the concatenation of 'snow' and 'ball' is 'snowball'.[1] SQL has a dedicated concatenation operator, ||.[1] In PostgreSQL, concat_ws takes its first argument as the separator and ignores other NULL arguments, so concat_ws(',', 'abcde', 2, NULL, 22) gives abcde,2,22.[8] In PostgreSQL, the || operator converts a non-string input to text when the other input is a string, so 'Value: ' || 42 gives Value: 42.[8] Polars' concat_str horizontally concatenates columns into a single string column, using an empty string as its default separator.[5]

OpenRefine's Join columns joins the selected columns in the order the user drags them into, and writes the result to the original column or to a new one.[6] Apache Hop's Concat fields transform always writes to a new target field, and can remove the fields it concatenated from the output.[7] The order of the parts is part of the result, so name the columns in the order a reader expects to see them. (Meridian's view)

Sources

  1. [1]Concatenation - Wikipediasecondary source
  2. [2]Using the Concatenate Columns transform to append columns - AWS Gluedocumentation
  3. [3]Null (SQL) - Wikipediasecondary source
  4. [4]Text Functions – DuckDBdocumentation
  5. [5]polars.concat_str — Polars documentationdocumentation
  6. [6]Column editing | OpenRefinedocumentation
  7. [7]Concat fields | Apache Hopdocumentation
  8. [8]PostgreSQL: Documentation: 18: 9.4. String Functions and Operatorsdocumentation

Used in

4 Protocols use this operation: