T SQL 条件字符串连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4157797/
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
T SQL Conditional String Concatenation
提问by jim
Have a 5 columns of address data. I need to concatenate these fields into a single address with spaces in between the values if they exist. If the column has a null value I should skip it and not enter any space.
有 5 列地址数据。我需要将这些字段连接到一个地址中，如果它们存在，则值之间有空格。如果该列有一个空值，我应该跳过它并且不输入任何空格。
select
case
when street_number != '' THEN (cast(street_number as int))
end as street_number,
case
when street_ext != '' then
case
when street_ext = 50 then '1/2'
end
end as street_ext,
case
when street_direct ! = '' then street_direct
end as street_direct,
case
when site_street ! = '' then site_street
end as site_street,
case
when site_address ! = '' then site_address
end as site_address
from parcel
what I'd like to do is have a variable and assign it to the value of the first column street_number, then when I move on to the next column, street_ext, if it isn't null I'd like to check to see if the variable is null and if not, append a space and the value...and so on down the road.
我想做的是有一个变量并将其分配给第一列 street_number 的值，然后当我移动到下一列 street_ext 时，如果它不为空，我想检查一下变量为空，如果不是，则附加一个空格和值...等等。
I'm rusty as hell and could use a push in the right direction.
我生锈了，可以朝正确的方向推动。
Thanks everyone.
谢谢大家。
回答by OMG Ponies
Use the "+" to concatenate strings in TSQL:
在 TSQL 中使用“+”连接字符串：
SELECT CASE
WHEN LEN(p.street_number) > 0 THEN p.street_number + ' '
ELSE ''
END +
CASE
WHEN p.street_ext = 50 THEN '1/2'
WHEN LEN(p.street_ext) > 0 THEN ''
ELSE p.street_ext
END + ' ' +
CASE
WHEN LEN(p.street_direct) > 0 THEN p.street_direct + ' '
ELSE ''
END +
CASE
WHEN LEN(p.site_street) > 0 THEN p.site_street + ' '
ELSE ''
END +
CASE
WHEN LEN(p.site_address) > 0 THEN p.site_address + ' '
ELSE ''
END AS full_address
FROM PARCEL p
The LEN functionreturns zero if the string value is NULL, or a zero length string.
该LEN函数返回零如果字符串值是NULL，或一个零长度的字符串。
回答by Mike Burton
Nested isnulls could do what you need. Something like:
嵌套的 isnulls 可以满足您的需求。就像是：
SELECT
ISNULL(streetnumber + ' ', '')
+ ISNULL(streetext + ' ', '')
etc
relying on the fact that NULL + ' ' = NULL.
依赖于 NULL + ' ' = NULL 的事实。
回答by chezy525
Something along the lines of:
类似的东西：
select coalesce(street_number+' ','')+
coalesce(case when street_ext=50 then '1/2' else null end+' ','')+
coalesce(street_direct+' ','')+
coalesce(site_street+' ','')+
coalesce(site_address,'')
from parcel
回答by RedFilter
I have assumed your data types are all varchar or similar for simplicity. If you are OK with removing any double spaces, how about:
为简单起见，我假设您的数据类型都是 varchar 或类似的。如果您可以删除任何双空格，那么如何：
rtrim(ltrim(replace(isnull(street_number) + ' '
+ isnull(street_ext) + ' '
+ isnull(street_direct) + ' '
+ isnull(site_street) + ' '
+ isnull(site_address), ' ', ' ')))
回答by WonderWorker
First I would declare the seperator as a variable, because customers are notorious for changing these.
首先，我会将分隔符声明为变量，因为客户因更改这些而臭名昭著。
I would do this as follows:
我会这样做：
DECLARE @AddressSeperator NVARCHAR(5) = ' '
...and then for the column declation, I'd use the following:
...然后对于列声明，我将使用以下内容：
, CONCAT
(
(CASE WHEN LEN(p.street_number) > 0 THEN p.street_number + @AddressSeperator ELSE '' END)
, (CASE WHEN p.street_ext = 50 THEN '1/2' + @AddressSeperator WHEN LEN(p.street_ext) > 0 THEN p.street_ext + @AddressSeperator ELSE '' END)
, (CASE WHEN LEN(p.street_direct) > 0 THEN p.street_direct + @AddressSeperator ELSE '' END)
, (CASE WHEN LEN(p.site_street) > 0 THEN p.site_street + @AddressSeperator ELSE '' END)
, ISNULL(p.site_address, '')
) AS [full_address]
