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:

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

a b
10 1234

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

  1. SELECT hd + ' Gb' volume FROM PC WHERE model = 1232;
we'll get this error message:

Error converting data type varchar to real.

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:

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

5 Gb
10 Gb
8 Gb
10 Gb

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.

  1. 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

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

plus concat
NULL concatenation with NULL

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

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

a b
10 1234


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.

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

concat concat_ws concat_ws_null
(NULL) concatenation with NULL 1, 2, 4

Suggested exercises: 119

Bookmark and Share
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100