SQL String Concatenation: MySQL, PostgreSQL, SQLite, SQL Server & Oracle

String concatenation syntax and NULL behavior differ significantly across relational database management systems. An operator that concatenates strings in one database may perform numeric addition or logical evaluation in another, and NULL arguments can either turn the entire result into NULL or be ignored as empty text.

These examples compare the concatenation operators, standard functions, delimiter handling, and NULL propagation rules across MySQL, MariaDB, PostgreSQL, SQLite, SQL Server, and Oracle.

Quick reference

MySQL and MariaDB

In standard MySQL and MariaDB configurations, the plus sign + performs arithmetic addition, not concatenation. Attempting 'Hello' + 'World' converts non-numeric strings to 0 and returns 0. Similarly, || functions as the logical OR operator by default unless the PIPES_AS_CONCAT SQL mode is active.

Use the CONCAT() function to join strings:

SELECT CONCAT('Post', '#', 42) AS result;

result
-------
Post#42

MySQL NULL handling and CONCAT_WS()

If any argument passed to CONCAT() is NULL, MySQL and MariaDB return NULL:

SELECT CONCAT('User: ', NULL) AS result;

result
-------
NULL

To concatenate strings while skipping NULL entries or joining them with a separator, use CONCAT_WS():

SELECT CONCAT_WS(' ', 'Jane', NULL, 'Doe') AS full_name;

full_name
---------
Jane Doe

See the MySQL 8.4 CONCAT() reference and SQLiz’s MySQL CONCAT() guide and MariaDB CONCAT() guide.

PostgreSQL

PostgreSQL supports both the ANSI SQL string concatenation operator || and the concat() function. Non-string inputs with concat() are automatically converted to text representations.

SELECT 'Item ' || 101 AS op_result,
       concat('Item ', 101) AS fn_result;

op_result | fn_result
----------+----------
Item 101  | Item 101

The || versus concat() difference in PostgreSQL

A key distinction in PostgreSQL is how NULL is treated:

1. The || operator returns NULL if any operand is NULL.
2. The concat() and concat_ws() functions treat NULL arguments as empty strings, preserving the remaining content.

SELECT 'alpha' || NULL AS op_null,
       concat('alpha', NULL, 'beta') AS fn_null;

op_null | fn_null
--------+----------
NULL    | alphabeta

Use concat_ws() when inserting a separator between non-null values:

SELECT concat_ws(', ', 'Red', NULL, 'Blue') AS colors;

colors
----------
Red, Blue

See PostgreSQL’s string functions documentation and SQLiz’s PostgreSQL concat() reference.

SQLite

SQLite uses the standard SQL double-pipe || operator for string concatenation. The + operator in SQLite is strictly numeric addition.

SELECT 'Version: ' || 3 || '.' || 46 AS version_string;

version_string
--------------
Version: 3.46

SQLite NULL handling

In SQLite, concatenating any value with NULL using || returns NULL:

SELECT 'Prefix: ' || NULL AS result;

result
------
NULL

If you need to skip NULL values or replace them with fallback text in SQLite, combine || with the ifnull() or coalesce() function:

SELECT 'Prefix: ' || ifnull(NULL, '') AS result;

result
-------
Prefix: 

See SQLite’s operators documentation.

SQL Server (Transact-SQL)

SQL Server provides both the + operator and the built-in CONCAT() and CONCAT_WS() functions (introduced in SQL Server 2012 and 2017).

Using the + operator requires all arguments to be compatible data types, or explicitly cast to character types. If SET CONCAT_NULL_YIELDS_NULL is ON (default), concatenating NULL produces NULL:

SELECT 'Order: ' + CAST(1001 AS varchar(10)) AS order_num;

order_num
-----------
Order: 1001

SQL Server CONCAT() and CONCAT_WS()

CONCAT() in SQL Server automatically converts non-string arguments to strings and converts NULL values to empty strings '':

SELECT CONCAT('Invoice #', 500, ' - ', NULL, 'Paid') AS invoice_status;

invoice_status
-------------------
Invoice #500 - Paid

Use CONCAT_WS() to join values with a specified delimiter while ignoring NULL entries:

SELECT CONCAT_WS('-', '2026', '10', '02') AS date_slug;

date_slug
----------
2026-10-02

See Microsoft’s CONCAT (Transact-SQL) documentation and SQLiz’s SQL Server CONCAT() reference.

Oracle

Oracle Database uses the ANSI SQL || operator as its primary string concatenation mechanism.

SELECT 'Employee: ' || 205 AS employee_tag FROM dual;

EMPLOYEE_TAG
------------
Employee: 205

Oracle’s two-argument CONCAT() limit

Unlike MySQL, PostgreSQL, or SQL Server, Oracle’s CONCAT(char1, char2) function accepts strictly two arguments. To concatenate three or more values using the function, calls must be nested:

-- Nested function calls:
SELECT CONCAT(CONCAT('A', 'B'), 'C') AS nested_concat FROM dual;

-- Idiomatic operator syntax:
SELECT 'A' || 'B' || 'C' AS pipe_concat FROM dual;

Oracle NULL and empty string semantics

In Oracle Database, an empty string '' is treated as NULL. However, the concatenation operator || treats NULL operands as zero-length strings rather than causing the whole result to become NULL:

SELECT 'Start-' || NULL || '-End' AS result FROM dual;

RESULT
---------
Start--End

See Oracle’s Concatenation Operator manual and SQLiz’s Oracle CONCAT() reference.

Key takeaways and portability tips

1. Avoid + for string concatenation: In MySQL, MariaDB, SQLite, and Oracle, + is an arithmetic operator. Only SQL Server uses + for strings, and even in SQL Server, CONCAT() is preferred because it handles NULL and data type conversion automatically.
2. Be deliberate about NULL handling: If you use || in PostgreSQL or SQLite, or CONCAT() in MySQL, any NULL operand results in a NULL output. Use CONCAT_WS() or wrap nullable columns in COALESCE() to avoid unexpected blank records.
3. Delimiter joins: When joining multiple address lines, names, or slugs, use CONCAT_WS() where supported (MySQL, MariaDB, PostgreSQL, SQL Server).