Archive

Posts Tagged ‘string joining’

SQL Server – "Denali” – String Concatenations using CONCAT()

25.07.2011 2 comments

“Denali” introduces a new function CONCAT() for string concatenation. CONCAT() returns a string by concatenating two or more strings. CONCAT() requires 2 to 254 arguments.

for example, CONCAT() can be used as below:

SELECT CONCAT('SQL ', 'Server') AS Result

Result Set:

Result

———-

SQL Server

 

(1 row(s) affected)

The same can be achieved by using '+' operator:

SELECT 'SQL ' + 'Server' AS Result

Result Set:

Result

———-

SQL Server

 

(1 row(s) affected)

Then, why to use CONCAT() ?, There are two main reasons to use CONCAT() instead of '+':

1. All arguments to CONCAT() are converted implicitly to string type, when using '+' you
    need to user CAST() or CONVERT() to string type.
2. NULLs are converted to empty string, when using '+', the result will be NULL, (anything +
    NULL = NULL)

Below example demonstrates the use of CONCAT() with NULL values and different data types.

CREATE TABLE tempTable

(

       Region        VARCHAR(30),

       Name          VARCHAR(30),

       SalesYTD      NUMERIC(14,2)

)

 

 

INSERT tempTable VALUES

('North America', 'Northwest', 123237.00 ),

('South America', 'Northwest',  37534.00 ),

('South America', NULL,         39667.00 ),

('North America', 'Southwest', 164232.00 )

 

SELECT CONCAT(Region, ' ', Name, ' ', SalesYTD) AS Result

FROM   tempTable

Result Set:

Result

——————————————————————————————————-

North America Northwest 123237.00

South America Northwest 37534.00

South America  39667.00

North America Southwest 164232.00

 

(4 row(s) affected)

If you try using '+' operator to concatenate, you will receive a data type conversion error:

SELECT Region + ' ' + Name + ' ' + SalesYTD AS Result

FROM   tempTable

Result Set:

Result

—————————————

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to numeric.

And, NULL value in any of the inputs will result in NULL:

SELECT Region + ' ' + Name AS Result

FROM   tempTable

Result Set:

Result

————————————————————-

North America Northwest

South America Northwest

NULL

North America Southwest

 

(4 row(s) affected)

To get proper result using '+' operator, you can use COALESCE() to replace NULLs with empty string and CAST() to convert input data type to string:

SELECT Region + ' ' + COALESCE(Name,'') + ' ' +

       CAST(SalesYTD AS VARCHAR(20)) AS Result

FROM   tempTable

Result Set:

Result

———————————————————————————-

North America Northwest 123237.00

South America Northwest 37534.00

South America  39667.00

North America Southwest 164232.00

 

(4 row(s) affected)

 

Hope This Helps!

Vishal