SQL 将两个数据库列连接成一个结果集列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6427764/
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
concatenate two database columns into one resultset column
提问by jilt3d
I use the following SQL to concatenate several database columns from one table into one column in the result set:
我使用以下 SQL 将一个表中的多个数据库列连接到结果集中的一列中：
SELECT (field1 + '' + field2 + '' + field3) FROM table1
SELECT (field1 + '' + field2 + '' + field3) FROM table1
When one of the fields is null I got null result for the whole concatenation expression. How can I overcome this?
当其中一个字段为空时，整个连接表达式的结果为空。我怎样才能克服这个问题？
The database is MS SQL Server 2008. By the way, is this the best way to concatenate database columns? Is there any standard SQL doing this?
数据库是 MS SQL Server 2008。顺便说一句，这是连接数据库列的最佳方式吗？是否有任何标准的 SQL 这样做？
采纳答案by Steve Prentice
The SQL standard way of doing this would be:
执行此操作的 SQL 标准方法是：
SELECT COALESCE(field1, '') || COALESCE(field2, '') || COALESCE(field3, '') FROM table1
Example:
例子：
INSERT INTO table1 VALUES ('hello', null, 'world');
SELECT COALESCE(field1, '') || COALESCE(field2, '') || COALESCE(field3, '') FROM table1;
helloworld
回答by BornToCode
If you were using SQL 2012 or above you could use the CONCAT function:
如果您使用的是 SQL 2012 或更高版本，则可以使用 CONCAT 函数：
SELECT CONCAT(field1, field2, field3) FROM table1
NULL fields won't break your concatenation.
NULL 字段不会破坏您的串联。
@bummi - Thanks for the comment - edited my answer to correspond to it.
@bummi - 感谢您的评论 - 编辑了我的答案以与其相对应。
回答by MatBailie
Normal behaviour with NULL is that any operation including a NULL yields a NULL...
NULL 的正常行为是任何包含 NULL 的操作都会产生一个 NULL...
- 9 * NULL = NULL
- NULL + '' = NULL
- etc
To overcome this use ISNULL or COALESCE to replace any instances of NULL with something else..
为了克服这个问题，使用 ISNULL 或 COALESCE 用其他东西替换任何 NULL 实例..
SELECT (ISNULL(field1,'') + '' + ISNULL(field2,'') + '' + ISNULL(field3,'')) FROM table1
回答by IAmTimCorey
If you are having a problem with NULL values, use the COALESCE function to replace the NULL with the value of your choice. Your query would then look like this:
如果您遇到 NULL 值的问题，请使用 COALESCE 函数将 NULL 替换为您选择的值。您的查询将如下所示：
SELECT (COALESCE(field1, '') + '' + COALESCE(field2, '') + '' + COALESCE(field3,'')) FROM table1
回答by Brian Scott
Use ISNULL to overcome it.
使用 ISNULL 来克服它。
Example:
例子：
SELECT (ISNULL(field1, '') + '' + ISNULL(field2, '')+ '' + ISNULL(field3, '')) FROM table1
This will then replace your NULL content with an empty string which will preserve the concatentation operation from evaluating as an overall NULL result.
然后，这将用空字符串替换您的 NULL 内容，这将保护串联操作不被评估为整体 NULL 结果。
回答by Pramod Pandav
If both Column are numeric Then Use This code
如果两列都是数字，则使用此代码
Just Cast Column As Varchar(Size)
只需将列转换为 Varchar（大小）
Example:
例子：
Select (Cast(Col1 as Varchar(20)) + '-' + Cast(Col2 as Varchar(20))) As Col3 from Table
回答by raman
Just Cast Column As Varchar(Size)
只需将列转换为 Varchar（大小）
If both Column are numeric then use code below.
如果两列都是数字，则使用下面的代码。
Example:
例子：
Select (Cast(Col1 as Varchar(20)) + '-' + Cast(Col2 as Varchar(20))) As Col3 from Table
What will be the size of col3it will be 40 or something else
col3它的大小将是 40 或其他东西
