将两个数据库列合并为一个结果集列 [英] concatenate two database columns into one resultset column

问题描述

我使用以下SQL将一个表中的多个数据库列连接到结果集中的一列：

I use the following SQL to concatenate several database columns from one table into one column in the result set:

SELECT（field1 +' '+ field2 +''+ field3）FROM table1

当其中一个字段为null时，整个串联表达式的结果为null。我该如何克服呢？

When one of the fields is null I got null result for the whole concatenation expression. How can I overcome this?

数据库是MS SQL Server2008。顺便问一下，这是连接数据库列的最佳方法吗？

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?

推荐答案

SQL的标准方法是：

The SQL standard way of doing this would be:

SELECT COALESCE(field1, '') || COALESCE(field2, '') || COALESCE(field3, '') FROM table1

示例：

INSERT INTO table1 VALUES ('hello', null, 'world');
SELECT COALESCE(field1, '') || COALESCE(field2, '') || COALESCE(field3, '') FROM table1;

helloworld

这篇关于将两个数据库列合并为一个结果集列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！