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)
This question is related to
sql
sql-server-2012
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.
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;
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.
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 = ''
;
SELECT CONCAT(LOWER(LAST_NAME), UPPER(LAST_NAME)
INITCAP(LAST_NAME), HIRE DATE AS ‘up_low_init_hdate’)
FROM EMPLOYEES
WHERE HIRE DATE = 1995
Source: Stackoverflow.com