The CONCAT function in SQL Server 2012 is Simple Yet Powerful

Continuing my series on SQL Server 2012, today we will explore the CONCAT function in SQL Server 2012. In versions prior to SQL Server 2012, a plus symbol “+” can be used to concatenate different string values to make a single string value. In version 2012, this is very easy with new CONCAT system function

Suppose you want to concatenate two strings 'test1' and 'test2' with space, you can use the old method

select 'test1'+' '+'test2'

which produces the result 'test1 test2'

If you want to concatenate values with mixed data types, you need to convert non character data types into character datatype. If you want to concatenate 'test1' with 100, you need to use

select 'test1'+' '+cast(100 as char(3))

which produces the result 'test1 100'

If NULL values is involved, the result is always NULL

select 'test1'+' '+cast(100 as char(3))+null

You need to use ISNULL or the COALESCE function to convert null to empty string

select 'test1'+' '+cast(100 as char(3))+isnull(null,'')

which produces the result 'test1 100'

But all these conversions are not needed when you use CONCAT function in SQL Server 2012

So all the above statements can be written as:

select concat('test1',' ','test2') as data
select concat('test1',' ',100) as data
select concat('test1',' ',100,null) as data
select concat('test1',' ',100,null,' ',getdate()) as data

CONCAT function does all the conversions and takes care of NULL by omitting it. So it is very simple and flexible.

OUTPUT