SQL Server: best way to concatenate multiple columns?

Question

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

I tried the new CONCAT() function, but it does not work when I use more than two columns.

So I wonder if this is the best way to solve the problem:

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

I cannot use COLUMN1 + COLUMN2 due to NULL values.

EDIT

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

CONCAT requires 2 arguments

Answer 1

It is clear in the discourse that the problem is to use VS2010 to write the request, since it uses the canonical function CONCAT() , which is limited by two parameters. There is probably a way to change this, but I do not know about it.

Alternative:

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

This approach requires non-string values ​​to be converted to strings, as well as NULL using ISNULL() or COALESCE() :

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

Answer 2

 SELECT CONCAT(LOWER(LAST_NAME), UPPER(LAST_NAME) INITCAP(LAST_NAME), HIRE DATE AS 'up_low_init_hdate') FROM EMPLOYEES WHERE HIRE DATE = 1995 

Answer 3

Try using below:

 SELECT (RTRIM(LTRIM(col_1))) + (RTRIM(LTRIM(col_2))) AS Col_newname, col_1, col_2 FROM s_cols WHERE col_any_condition = '' ; 

Answer 4

Blockquote

Using concatenation in Oracle SQL is very simple and interesting. But I don’t know much about MS-SQL.

Blockquote

Here we are looking for Oracle: Syntax: SQL> select First_name||Last_Name as Employee from employees;

Result: EMPLOYEE

EllenAbel SundarAnde MozheAtkinson

Here AS: a keyword used as an alias. We can combine null values. for example: columnm1 || Null

Suppose any of your columns contains a NULL value, then the result will only show the value of that column that has a value.

You can also use a literal string of characters in concatenation.

eg. select column1||' is a '||column2 from tableName; Result: column1 - column2. between the literal letters must be written in single quotes. you cna exclude numbers.

NOTE This applies only to oracle // SQL server.

Answer 5

If the fields are NULL, you will have to process these values. Remember that null is contagious, and concat('foo', null) just results in NULL :

 SELECT CONCAT(ISNULL(column1, ''),ISNULL(column2,'')) etc... 

Basically check each field for nullness and replace an empty string if that is the case.