SQL99 defines a concatenation operator ( || ), which joins two distinct strings into one string value. The CONCATENATE function appends two or more strings together, producing a single output string. PostgreSQL and Oracle support the double-concatenation operator. Microsoft SQL Server uses the plus sign (+) concatenation operator.

MySQL supports a similar function, CONCAT( ). Refer to Concatenation Operatorsin Chapter 3, for more information on concatenation within Oracle, PostgreSQL, and Microsoft SQL Server.

SQL99 Syntax

CONCATENATE('string1' || 'string2')

MySQL Syntax

CONCAT(str1, str2, [,...n])

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...');

Get SQL in a Nutshell now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.