SQL in a Nutshell, 3rd Edition

Name

Concatenation Operator

ANSI SQL defines a concatenation operator (||), which joins two distinct strings into one string value.

MySQL

MySQL supports CONCAT as a synonym for the ANSI SQL concatenation operator and uses the || operator for logical OR.

Oracle and PostgreSQL

Oracle and PostgreSQL support the ANSI SQL double vertical bar (||) concatenation operator. Oracle also supports CONCAT as a synonym for the ANSI SQL operator.

SQL Server

SQL Server uses the plus sign (+) as a synonym for the ANSI SQL concatenation operator. SQL Server has the system setting CONCAT_NULL_YIELDS_NULL, which can be set to alter the behavior when NULL values are used in the concatenation of string values.

Examples

/* ANSI SQL Syntax */
'string1' || 'string2' || 'string3'
'string1string2string3'

/* On MySQL */
CONCAT('string1', 'string2')
'string1string2'

If any of the concatenation values are NULL, the entire returned string is NULL. Also, if a numeric value is concatenated, it is implicitly converted to a character string.

SELECT CONCAT('My ', 'bologna ', 'has ', 'a ', 'first ', 'name...');
'My bologna has a first name...'
SELECT CONCAT('My ', NULL, 'has ', 'first ', 'name...');
NULL