CONCAT function

SQL Server uses “+” operator for concatenation of strings.

I.e. if operands are numeric, the sum is being calculated, if operands are strings, we have concatenation:

SELECT 1+2+3+4 a, '1'+'2'+'3'+'4' b;

If operands are of different data types, SQL Server performs implicit type conversion. When executing the following query

SELECT hd + ' Gb' volume FROM PC WHERE model = 1232;

There is type priority when implicit conversion is being performed, and this priority causes the server to transform the character string ’ Gb’ to the data type of the hd column (real data type).

Needless to say that the explicit type conversion solves the problem:

SELECT CAST(hd AS VARCHAR) + ' Gb' volume FROM PC WHERE model = 1232;

CONCAT function is in SQL Server since version 2012. This function concatenates its arguments been implicitly converting them to character string data type. With aid of this function the above query can be rewritten as follows.

SELECT CONCAT(hd, ' Gb') volume FROM PC WHERE model=1232;

Another useful feature of CONCAT function is that NULL values are being implicitly converted to empty string - ‘’. Ordinary concatenation with NULL gives NULL. Here is example

SELECT NULL + 'concatenation with NULL' plus,
               CONCAT(NULL, 'concatenation with NULL') concat;

It should be noted that CONCAT function has variable number of parameters, but not less than two.

SELECT 1+2+3+4 a, CONCAT(1, 2, 3, 4) b;

MySQL

MySQL has CONCAT function also, even two functions. The first one - CONCAT - return NULL if anyone of parameters is NULL. The second one - CONCAT_WS - ignores the NULL valued argument. Moreover, this function include separator as the first parameter. The separator is being placed between concatenated parameters.

SELECT CONCAT(NULL, 'concatenation with NULL') concat,
 CONCAT_WS('',NULL, 'concatenation with NULL') concat_ws,
CONCAT_WS(', ',1, 2, NULL, 4) concat_ws_null;

Suggested exercises: 119