concatenate null value columns in Tsql

I use + to concatenate sever开发者_Go百科al columns's value. But + doesnt work if one of that columns has null value. For example

Select null+ 'Test'

query returns null instead of 'Test'.

What are your advices to solve that problem?

On versions prior to SQL Server 2012 you should use

   Select ISNULL(YourColumn,'') + 'Test' /*Or COALESCE(YourColumn,'')*/

to avoid this issue.

There is a connection option SET CONCAT_NULL_YIELDS_NULL OFF but that is deprecated.

SQL Server 2012 introduces the CONCAT function that treats NULL as an empty string when concatenating.

SELECT CONCAT(null,'Test')

Use IsNull :

SELECT IsNull(MyColumn, '') + 'Test' ...