SQL Server：连接多列的最佳方式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24371044/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: Best way to concatenate multiple columns?
提问by D. Caan
I am trying to concatenate multiple columns in a query in SQL Server 11.00.3393.
我试图在 SQL Server 11.00.3393 的查询中连接多个列。
I tried the new function CONCAT()but it's not working when I use more than two columns.
我尝试了新功能，CONCAT()但是当我使用两列以上时它不起作用。
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 + COLUMN2because of NULLvalues.
COLUMN1 + COLUMN2由于NULL值，我无法使用。
EDIT
编辑
If I try SELECT CONCAT('1','2','3') AS RESULTI get an error
如果我尝试SELECT CONCAT('1','2','3') AS RESULT我得到一个错误
The CONCAT function requires 2 argument(s)
CONCAT 函数需要 2 个参数
回答by Hart CO
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.
通过讨论，很明显问题在于使用 VS2010 编写查询，因为它使用了CONCAT()仅限于 2 个参数的规范函数。可能有一种方法可以改变这种情况，但我不知道。
An alternative:
替代：
SELECT '1'+'2'+'3'
This approach requires non-string values to be cast/converted to strings, as well as NULLhandling via ISNULL()or COALESCE():
这种方法需要将非字符串值强制转换/转换为字符串，以及NULL通过ISNULL()or 进行处理COALESCE()：
SELECT ISNULL(CAST(Col1 AS VARCHAR(50)),'')
+ COALESCE(CONVERT(VARCHAR(50),Col2),'')
回答by JUVA
SELECT CONCAT(LOWER(LAST_NAME), UPPER(LAST_NAME)
INITCAP(LAST_NAME), HIRE DATE AS ‘up_low_init_hdate')
FROM EMPLOYEES
WHERE HIRE DATE = 1995
回答by user7912960
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 = ''
;
回答by Suraj Shrivastava
Blockquote
块引用
Using concatenation in Oracle SQLis very easy and interesting. But don't know much about MS-SQL.
在Oracle SQL 中使用串联非常简单有趣。但是不太了解MS-SQL。
Blockquote
块引用
Here we go for Oracle :
Syntax:
SQL> select First_name||Last_Name as Employee
from employees;
在这里，我们使用 Oracle ： 语法：
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 NULLvalues. e.g. : columnm1||Null
这里AS：用作别名的关键字。我们可以连接NULL值。例如： columnm1||Null
Suppose any of your columns contains a NULLvalue then the result will show only the value of that column which has value.
假设您的任何列包含NULL值，那么结果将仅显示具有值的该列的值。
You can also use literal character stringin 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.
例如
select column1||' is a '||column2
from tableName;结果：column1 是 column2。in between 文字应该用单引号括起来。你可以排除数字。
NOTE: This is only for oracle server//SQL.
注意：这仅适用于 oracle 服务器//SQL。
回答by Marc B
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 NULLas well:
如果字段可以为空，则您必须处理这些空值。请记住，null 具有传染性，并且concat('foo', null)只会导致NULL：
SELECT CONCAT(ISNULL(column1, ''),ISNULL(column2,'')) etc...
Basically test each field for nullness, and replace with an empty string if so.
基本上测试每个字段是否为空，如果是，则用空字符串替换。
