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 myTableI can't use COLUMN1 + COLUMN2 because of NULL values.
EDIT
If I try SELECT CONCAT('1','2','3') AS RESULT I get an error
13The CONCAT function requires 2 argument(s)
6 Answers
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),'')SELECT CONCAT(LOWER(LAST_NAME), UPPER(LAST_NAME) INITCAP(LAST_NAME), HIRE DATE AS ‘up_low_init_hdate’)
FROM EMPLOYEES
WHERE HIRE DATE = 1995Try 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 = ''
;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> 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.
for anyone dealing with Snowflake
Try using CONCAT with multiple columns like so:
SELECT CONCAT(col1, col2, col3) AS all_string_columns_together , CONCAT(CAST(col4 AS VARCHAR(50), col1) AS string_and_int_column
FROM tableIf 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.
1
