sql合并了两个可能具有空值的列

This should be an easy thing to do but I seem to keep getting an extra space. Basically what I am trying to do is combine multiple columns into one column. BUT every single one of these columns might be null as well. When I combine them, I also want them to be separated by a space (' ').

这应该是一件容易的事情，但我似乎一直得到一个额外的空间。基本上，我要做的是将多个列合并到一个列中。但是每一列都可能是空的。当我合并它们时，我还希望它们被一个空格(')隔开。

What I created is the following query:

我创建的查询如下:

select 'All'= ISNULL(Name+' ','')+ISNULL(City+' ','')+ISNULL(CAST(Age as varchar(50))+' ','')  from zPerson

and the result is:

结果是:

All
John Rock Hill 23 
 Munchen 29 
Julie London 35 
 Fort Mill 27 
Bob  29

As you can see: there is an extra space when the name is null. I don't want that.

如您所见:当名称为空时，有一个额外的空间。我不希望这样。

The initial table is :

最初的表格是:

id  Name    City            Age InStates    AllCombined
1   John    Rock Hill       23  1              NULL
2           Munchen         29  0              NULL
3   Julie   London          35  0              NULL
4           Fort Mill       27  1              NULL
5   Bob                     29  1              NULL

Any ideas?

什么好主意吗?

3 个解决方案

#1

select 'All'= LTRIM(ISNULL(Name+' ','')+ISNULL(City+' ','')+ISNULL(CAST(Age as varchar(50))+' ','')  from zPerson)

SEE LTRIM()

看到LTRIM()

#2

In the data you have posted, the Name column contains no NULLs. Instead, it contains empty strings, so ISNULL(Name+' ','') will evalate to a single space.

在已发布的数据中，Name列不包含任何null。相反，它包含空字符串，所以ISNULL(Name+' '， ")将在一个空格中表示。

The simplest resolution is to change the data so that empty-strings are null. This is appropriate in your case since this is clearly your intention.

最简单的解决方案是修改数据，使空字符串为空。这在你的情况中是适当的，因为这显然是你的意图。

UPDATE zPerson SET Name=NULL WHERE Name=''

Repeat this for your City and Age fields if necessary.

如果需要的话，在你的城市和年龄领域重复这一点。

#3

Use TRIM() arount the ISNULL() function, or LTRIM() around the entire selected term

使用TRIM()将ISNULL()函数或LTRIM()加载到整个选择项上

#1

select 'All'= LTRIM(ISNULL(Name+' ','')+ISNULL(City+' ','')+ISNULL(CAST(Age as varchar(50))+' ','')  from zPerson)