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

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan