T SQL 条件字符串连接 [英] T SQL Conditional String Concatenation
问题描述
有 5 列地址数据.我需要将这些字段连接到一个地址中,如果它们存在,则值之间有空格.如果该列有一个空值,我应该跳过它并且不输入任何空格.
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.
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
我想做的是有一个变量并将其分配给第一列 street_number 的值,然后当我转到下一列 street_ext 时,如果它不为空,我想检查一下查看变量是否为空,如果不是,则附加一个空格和值...等等.
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.
我生锈了,可以朝正确的方向推动.
I'm rusty as hell and could use a push in the right direction.
谢谢大家.
推荐答案
在 TSQL 中使用+"连接字符串:
Use the "+" to concatenate strings in 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
LEN 函数如果字符串值为 NULL,则返回零,或零长度字符串.
The LEN function returns zero if the string value is NULL, or a zero length string.
这篇关于T SQL 条件字符串连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!