New sql features

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:

Console

Execute

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

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

    Scheme

No scheme

If operands are of different data types,  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server performs implicit type conversion. When executing the following query

Console

Execute

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

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

    Scheme

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:

Console

Execute

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

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

    Scheme

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.

Console

Execute

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

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

    Scheme

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

Console

Execute

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

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

    Scheme

No scheme

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

Console

Execute

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

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

    Scheme

No scheme

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