SQLite Concatenate Strings: || Operator, concat() and concat_ws()

Ninad Pathak

October 7, 2026

The || operator joins strings in SQLite, and version 3.44.0 added the concat() functions alongside it. They treat NULL differently: || turns the whole result into NULL, while concat() skips it. In this article I’ll walk you through every way to join strings in SQLite, and how to keep a NULL from wiping out your output.

TLDR

The concatenation operator is ||: first_name || ' ' || last_name.
If any value in a || chain is NULL, the whole result is NULL.

SQLite 3.44.0 and later also have concat(), which skips NULLs.
concat_ws() joins values with a separator and skips NULLs too.
To join values from many rows into one string, use group_concat(). Its alias is string_agg().

Don’t use + to join strings. It adds numbers.

What is string concatenation in SQLite?

String concatenation joins two or more strings into one. SQLite’s documentation puts it this way: “The || operator is “concatenate” – it joins together the two strings of its operands” (SQLite expressions). Since version 3.44.0 (November 2023), SQLite also has the concat() and concat_ws() functions, added “compatible with PostgreSQL, SQLServer, and MySQL” (SQLite 3.44.0 release notes).

SQLite concatenation syntax

Method	Syntax	NULL handling	Since
`\|\|` operator	`a \|\| b \|\| c`	any NULL makes the result NULL	always
`concat()`	`concat(a, b, c)`	skips NULL arguments	3.44.0
`concat_ws()`	`concat_ws(sep, a, b, c)`	skips NULL arguments, and their separators	3.44.0
`group_concat()` / `string_agg()`	`group_concat(col, sep)`	skips NULL values; joins rows, not columns	group_concat always; string_agg 3.44.0

The functions are described in SQLite core functions and SQLite aggregate functions.

How to concatenate strings in SQLite

To show you how each method behaves, I will use a small customers table where two of the three customers have no middle name:

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  middle_name TEXT,
  last_name TEXT NOT NULL,
  city TEXT,
  state TEXT
);
INSERT INTO customers (first_name, middle_name, last_name, city, state) VALUES
  ('Ada', NULL, 'Lovelace', 'London', NULL),
  ('Grace', 'Brewster', 'Hopper', 'Arlington', 'VA'),
  ('Linus', NULL, 'Torvalds', 'Portland', 'OR');
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  item TEXT NOT NULL
);
INSERT INTO orders (customer_id, item) VALUES
  (1, 'notebook'), (2, 'compiler'), (1, 'pen'), (2, 'manual'), (1, 'ink');

Step 1: Join literals with ||

Put || between each piece. Add the spaces yourself:

sqlite> SELECT 'Hello' || ' ' || 'World' AS greeting;
greeting
-----------
Hello World

Step 2: Concatenate two columns

The same operator joins column values. That’s how you build a full name from first and last name columns:

sqlite> SELECT first_name || ' ' || last_name AS full_name FROM customers;
full_name
--------------
Ada Lovelace
Grace Hopper
Linus Torvalds

Leave out the ' ' and the values run together:

sqlite> SELECT first_name || last_name AS full_name FROM customers WHERE id = 1;
full_name
-----------
AdaLovelace

Examples

Each example below runs against the same table, with NULLs shown as NULL.

Mix numbers and text

|| converts numbers to text, so you can build labels from IDs. Even two numbers come back as text:

sqlite> SELECT 'Order #' || id AS label, 3 || 4 AS digits, typeof(3 || 4) AS type FROM orders WHERE id = 1;
label     digits  type
--------  ------  ----
Order #1  34      text

See what NULL does to ||

Ada and Linus have no middle name, and their whole full name turns into NULL:

sqlite> SELECT first_name || ' ' || middle_name || ' ' || last_name AS full_name FROM customers;
full_name
---------------------
NULL
Grace Brewster Hopper
NULL

COALESCE() fixes it by swapping NULL for a value you choose. Putting the space inside it avoids a double space:

sqlite> SELECT first_name || COALESCE(' ' || middle_name, '') || ' ' || last_name AS full_name FROM customers;
full_name
---------------------
Ada Lovelace
Grace Brewster Hopper
Linus Torvalds

Use concat() to skip NULLs

concat() “returns a string which is the concatenation of the string representation of all of its non-NULL arguments” (SQLite core functions). The NULL middle names no longer wipe out the result, but the two spaces around them stay:

sqlite> SELECT concat(first_name, ' ', middle_name, ' ', last_name) AS full_name FROM customers;
full_name
---------------------
Ada  Lovelace
Grace Brewster Hopper
Linus  Torvalds

Join with a separator using concat_ws()

concat_ws() takes the separator first and only puts it between non-NULL values. That fixes both the names and a place column where one state is missing:

sqlite> SELECT concat_ws(' ', first_name, middle_name, last_name) AS full_name, concat_ws(', ', city, state) AS place FROM customers;
full_name              place
---------------------  -------------
Ada Lovelace           London
Grace Brewster Hopper  Arlington, VA
Linus Torvalds         Portland, OR

If the separator itself is NULL, the result is NULL:

sqlite> SELECT concat_ws(NULL, 'a', 'b') AS result;
result
------
NULL

Join values from many rows with group_concat()

group_concat() is an aggregate: it joins one column’s values across the rows of each group. Without an ORDER BY, “the order of the concatenated elements is arbitrary” (SQLite aggregate functions):

sqlite> SELECT c.first_name, group_concat(o.item, ', ') AS items FROM customers c JOIN orders o ON o.customer_id = c.id GROUP BY c.id;
first_name  items
----------  ------------------
Ada         notebook, pen, ink
Grace       compiler, manual

Since 3.44.0 you can add ORDER BY inside the call. string_agg(X, Y) is an alias for group_concat(X, Y). Its name matches PostgreSQL and SQL Server:

sqlite> SELECT c.first_name, group_concat(o.item, ', ' ORDER BY o.item) AS items, string_agg(o.item, ' | ' ORDER BY o.item DESC) AS reversed FROM customers c JOIN orders o ON o.customer_id = c.id GROUP BY c.id;
first_name  items               reversed
----------  ------------------  --------------------
Ada         ink, notebook, pen  pen | notebook | ink
Grace       compiler, manual    manual | compiler

Store a concatenated value

You can write the result into a column with UPDATE:

sqlite> ALTER TABLE customers ADD COLUMN full_name TEXT;
sqlite> UPDATE customers SET full_name = concat_ws(' ', first_name, last_name); SELECT id, full_name FROM customers;
id  full_name
--  --------------
1   Ada Lovelace
2   Grace Hopper
3   Linus Torvalds

A generated column keeps the joined value in step with its source columns automatically:

sqlite> CREATE TABLE people (first TEXT, last TEXT, display TEXT GENERATED ALWAYS AS (first || ' ' || last) VIRTUAL);
sqlite> INSERT INTO people (first, last) VALUES ('Alan', 'Turing'); SELECT * FROM people;
first  last    display
-----  ------  -----------
Alan   Turing  Alan Turing

Edge cases

The + operator isn’t concatenation. It’s numeric addition, so text that doesn’t look like a number counts as 0:

sqlite> SELECT 'abc' + 'def' AS plus_result, '3' + '4' AS numeric_text;
plus_result  numeric_text
-----------  ------------
0            7

Older SQLite versions don’t have the functions added in 3.44.0. Check yours with SELECT sqlite_version();. Before 3.44.0, use || with COALESCE() and group_concat().
MySQL treats || differently. In MySQL’s default mode, || means logical OR. See SQLite vs MySQL.

Conclusion

Use || when none of the values can be NULL, or wrap the nullable ones in COALESCE(). On SQLite 3.44.0 or later, concat_ws() is the simplest way to join columns with a separator, because it skips NULLs and their separators. To join values from many rows, use group_concat() with an ORDER BY.