SQL Server Best way to concatenate multiple columns

Question

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

User · Answer

If the fields are nullable  then you ll have to handle those nulls  Remember that null is contagious  and concat  foo   null  simply results in NULL as well   SELECT CONCAT ISNULL column1      ISNULL column2      etc      Basically test each field for nullness  and replace with an empty string if so

User · Answer

SELECT CONCAT LOWER LAST NAME   UPPER LAST NAME         INITCAP LAST NAME   HIRE DATE AS    up low init hdate     FROM EMPLOYEES WHERE HIRE DATE   1995

User · 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

User · Answer

Blockquote   Using concatenation in Oracle SQL is very easy and interesting  But don t know much about MS-SQL      Blockquote   Here we go for Oracle    Syntax   SQL gt  select  First name  Last Name as Employee         from employees   Result  EMPLOYEE  EllenAbel SundarAnde MozheAtkinson  Here AS  keyword used as alias  We can concatenate with NULL values  e g    columnm1  Null  Suppose any of your columns contains a NULL value then the result will show only the value of that column which has value    You can also use literal character string in concatenation    e g   select column1    is a    column2 from tableName  Result  column1 is a column2  in between literal should be encolsed in single quotation  you cna exclude numbers   NOTE  This is only for oracle server  SQL

User · Answer

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

[sql] SQL Server: Best way to concatenate multiple columns?

Examples related to sql

Examples related to sql-server-2012