SQL in a Nutshell, 2nd Edition

Name

Concatenation Operator

Synopsis

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

DB2

DB2 supports the SQL2003 concatenation operator and the CONCAT function as a synonym.

MySQL

MySQL supports CONCAT( ) as a synonym for the SQL2003 concatenation operator.

Oracle and PostgreSQL

PostgreSQL and Oracle support the SQL2003 double vertical bar concatenation operator.

SQL Server

SQL Server uses the plus sign (+) as a synonym for the SQL2003 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

/* SQL2003 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