SQL Server: Best way to concatenate multiple columns?

I am trying to concatenate multiple columns in a query in SQL Server 11.00.3393.

I tried the new function CONCAT() but it’s not working when I use more than two columns.

So I wonder if that’s the best way to solve the problem:

SELECT CONCAT(CONCAT(CONCAT(COLUMN1,COLUMN2),COLUMN3),COLUMN4) FROM myTable

I can’t use COLUMN1 + COLUMN2 because of NULL values.

EDIT

If I try SELECT CONCAT('1','2','3') AS RESULT I get an error

The CONCAT function requires 2 argument(s)

Answer

Through discourse it’s clear that the problem lies in using VS2010 to write the query, as it uses the canonical CONCAT() function which is limited to 2 parameters. There’s probably a way to change that, but I’m not aware of it.

An alternative:

SELECT '1'+'2'+'3'

This approach requires non-string values to be cast/converted to strings, as well as NULL handling via ISNULL() or COALESCE():

SELECT  ISNULL(CAST(Col1 AS VARCHAR(50)),'')
      + COALESCE(CONVERT(VARCHAR(50),Col2),'')