blocks|key|550940|text|您可以使用COALESCE|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|550941|SELECT
++COALESCE+(street,+'')+%2B
++COALESCE+(city,+'')+%2B
++state+%2B
++zip+AS+address|code-block|syntax|javascript|550942|另一种方法是使用ISNULL|550943|SELECT
++ISNULL(street,+'')+%2B
++ISNULL(city,+'')+%2B
++state+%2B
++zip+AS+address|550944|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms190349.aspx|1|http://msdn.microsoft.com/en-us/library/ms184325.aspx^0|5|8|5|8|0|0|0|8|6|8|6|1|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@$9|11|A|12|1|13]]|E|$]]|$1|F|3|G|5|H|7|14|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|15|8|@$9|16|A|17|B|C]]|D|@$9|18|A|19|1|1A]]|E|$]]|$1|M|3|N|5|H|7|1B|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|1C|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]|W|$5|R|S|T|E|$U|X]]]]

You can use <a href="http://msdn.microsoft.com/en-us/library/ms190349.aspx" rel="nofollow"><code>COALESCE</code></a>:

<pre><code>SELECT
 COALESCE (street, '') +
 COALESCE (city, '') +
 state +
 zip AS address
</code></pre>

An alternative is to use <a href="http://msdn.microsoft.com/en-us/library/ms184325.aspx" rel="nofollow"><code>ISNULL</code></a>:

<pre><code>SELECT
 ISNULL(street, '') +
 ISNULL(city, '') +
 state +
 zip AS address
</code></pre>

blocks|key|2640704|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2640705|select
++++isnull(street+%2B+',',+'')+%2B
++++isnull(city+%2B+',',+'')+%2B
++++isnull(state+%2B+',',+'')+%2B
++++isnull(zip+%2B+',',+'')|code-block|syntax|javascript|2640706|这依赖于标准的NULL行为，即对NULL的任何操作都会返回NULL。因此，如果street为NULL，则street+%2B+'，‘将返回NULL。然后，ISNULL运算符将选择空字符串。|offset|length|2640707|有关NULL连接的更多信息，请参见SET+CONCAT_NULL_YIELDS_NULL。(在将来的版本中将始终启用此选项。)|2640708|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms184325.aspx|1|http://msdn.microsoft.com/en-us/library/ms176056.aspx^0|0|0|23|6|0|0|H|R|1|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@$I|Z|J|10|1|11]]|A|$]]|$1|K|3|L|5|6|7|12|8|@]|9|@$I|13|J|14|1|15]]|A|$]]|$1|M|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]|U|$5|P|Q|R|A|$S|V]]]]

Try this:

<pre><code>select
 isnull(street + ',', '') +
 isnull(city + ',', '') +
 isnull(state + ',', '') +
 isnull(zip + ',', '')
</code></pre>

This relies on the standard NULL behavior where any operation on NULL returns NULL. So street + ',' will return NULL if street is NULL. The <a href="http://msdn.microsoft.com/en-us/library/ms184325.aspx" rel="nofollow">ISNULL</a> operator will then choose the empty string instead.

For more information about NULL concatenation, see <a href="http://msdn.microsoft.com/en-us/library/ms176056.aspx" rel="nofollow">SET CONCAT_NULL_YIELDS_NULL</a>. (This option will always be enabled in a future version.)

blocks|key|4540101|text|declare+@temp+table+(street+varchar(100)+null,city+varchar(100)+null,+state+char(2)+null,+zip+varchar(10)+null)
insert+into+@temp+(street,city,state,zip)+values+(null,null,'AL','36609')

select+ISNULL(street+%2B+',+','')+%2B++ISNULL(city+%2B+',+','')+%2B+ISNULL(state+%2B+',+','')+%2B+ISNULL(zip,'')+from+@temp|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|4540102|会把你送回来|unstyled|4540103|AL,+36609|4540104|编辑|offset|length|style|BOLD|4540105|这不是很好，但似乎适用于各种null位置：|4540106|declare+@temp+table+(street+varchar(100)+null,city+varchar(100)+null,+state+char(2)+null,+zip+varchar(10)+null)
insert+into+@temp+(street,city,state,zip)+values+(null,null,'AL','36609')
insert+into+@temp+(street,city,state,zip)+values+(null,null,'AL',null)
insert+into+@temp+(street,city,state,zip)+values+('Acacia+Avenue',null,'AL',null)
insert+into+@temp+(street,city,state,zip)+values+('Acacia+Avenue',null,null,null)

select
++case+
++++when+city+is+null+and+state+is+null+and+zip+is+null+then+ISNULL(street,+'')
++++when+state+is+null+and+zip+is+null+then+ISNULL(street+%2B+',+','')+%2B++ISNULL(city,'')
++++when+zip+is+null+then+ISNULL(street+%2B+',+','')+%2B++ISNULL(city+%2B+',+','')+%2B+ISNULL(state,'')
++++else+ISNULL(street+%2B+',+','')+%2B++ISNULL(city+%2B+',+','')+%2B+ISNULL(state+%2B+',+','')+%2B+ISNULL(zip,'')+
end+
from+@temp|4540107|结果|4540108|AL,+36609
AL
Acacia+Avenue,+AL
Acacia+Avenue

(4+row(s)+affected)|4540109|entityMap^0|0|0|0|0|2|0|0|0|0|2|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|Z|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|10|8|@]|9|@]|A|$B|C]]|$1|I|3|J|5|F|7|11|8|@$K|12|L|13|M|N]]|9|@]|A|$]]|$1|O|3|P|5|F|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|15|8|@]|9|@]|A|$B|C]]|$1|S|3|T|5|F|7|16|8|@$K|17|L|18|M|N]]|9|@]|A|$]]|$1|U|3|V|5|6|7|19|8|@]|9|@]|A|$B|C]]|$1|W|3|-4|5|F|7|1A|8|@]|9|@]|A|$]]]|X|$]]

<pre><code>declare @temp table (street varchar(100) null,city varchar(100) null, state char(2) null, zip varchar(10) null)
insert into @temp (street,city,state,zip) values (null,null,'AL','36609')

select ISNULL(street + ', ','') + ISNULL(city + ', ','') + ISNULL(state + ', ','') + ISNULL(zip,'') from @temp
</code></pre>

will return you

<pre><code>AL, 36609
</code></pre>

EDIT

This is not pretty, but seems to work for a variety of null placements:

<pre><code>declare @temp table (street varchar(100) null,city varchar(100) null, state char(2) null, zip varchar(10) null)
insert into @temp (street,city,state,zip) values (null,null,'AL','36609')
insert into @temp (street,city,state,zip) values (null,null,'AL',null)
insert into @temp (street,city,state,zip) values ('Acacia Avenue',null,'AL',null)
insert into @temp (street,city,state,zip) values ('Acacia Avenue',null,null,null)

select
 case 
 when city is null and state is null and zip is null then ISNULL(street, '')
 when state is null and zip is null then ISNULL(street + ', ','') + ISNULL(city,'')
 when zip is null then ISNULL(street + ', ','') + ISNULL(city + ', ','') + ISNULL(state,'')
 else ISNULL(street + ', ','') + ISNULL(city + ', ','') + ISNULL(state + ', ','') + ISNULL(zip,'') 
end 
from @temp
</code></pre>

RESULTS

<pre><code>AL, 36609
AL
Acacia Avenue, AL
Acacia Avenue

(4 row(s) affected)
</code></pre>

blocks|key|550977|text|尝试此查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|550978|SELECT++SUBSTRING(
+++++ISNULL(','%2Bstreet,'')
++++%2BISNULL(','%2Bcity,'')
++++%2BISNULL(','%2Bstate,'')
++++%2BISNULL(','%2Bzip,'')
++++,2,+500)+AS+address
FROM++++table|code-block|syntax|javascript|550979|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this query:

<pre><code>SELECT SUBSTRING(
 ISNULL(','+street,'')
 +ISNULL(','+city,'')
 +ISNULL(','+state,'')
 +ISNULL(','+zip,'')
 ,2, 500) AS address
FROM table
</code></pre>

blocks|key|4540162|text|我会把它写成：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4540163|DECLARE+@Address+VARCHAR(200)
select+@Address=+isnull(','+%2B+street,+'')+%2B+isnull(','+%2B+city,+'')+%2B+
isnull(','+%2B+state,+++++'')+%2B+isnull(','+%2B+zip,+'')+from+yourtablename+where+criteria
SELECT+STUFF(@Address,+1,+1,+'')+AS+Address|code-block|syntax|javascript|4540164|正如其他成员已经指出的，当您对具有NULL的字段执行任何操作时，结果表达式(值)将始终为null。|4540165|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I'd write it as below :

<pre><code>DECLARE @Address VARCHAR(200)
select @Address= isnull(',' + street, '') + isnull(',' + city, '') + 
isnull(',' + state, '') + isnull(',' + zip, '') from yourtablename where criteria
SELECT STUFF(@Address, 1, 1, '') AS Address
</code></pre>

As already pointed out by other members, when you perform any operation on fields having NULL, resultant expression(value) would always be null.

i have address columns that i would like to concatenate even if only one field is not null.

for example

<pre><code>street,city,state,zip
null,null,AL,36609
</code></pre>

Currently, my sql statement is like this:

<pre><code>select street + ',' + city + ',' + state + ',' + 'zip as address
</code></pre>

so the sample record above gives me null for address. is there an easy way to show whatever is not nulll? 

so the example record above would return:

<pre><code>AL, 36609
</code></pre>

tsql help with concatenting several fields

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有想要连接的地址列，即使只有一个字段不为空。例如street,city,state,zipnull,null,AL,36609目前，我的sql语句是这样的：select street + ',' + city + ',' + state + ',' + 'zip as address因此，上面的示例记录给我的地址为空...

问tsql帮助连接多个字段
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问tsql帮助连接多个字段EN