在 SQL 语言中连接备用字符

Question

我必须在SQL中进行一些操作。

我需要在 ColC 中组合 ColA 和 ColB - 用 > 分隔的替代文本 - 如下所示：a1-b1> a2-b2>a3-b3; .

例

ColA            | ColB             | ColC
----------------+------------------+-----------------------------
abcd > e > efg  | ppppp > ppt > pp | abcd-ppppp > e-ppt > efg-pp 
hij > kl > iiii | aaa > bbb > hhh  | hij-aaa > kl-bbb > iiii-hhh
aa              | fff              | aa-fff
a > bbb         | pp > a           | a-pp > bbb-a

然后将ColD添加到最终结果 ColE 的每个字符串路径ColC>

开头

dc1 > dc2 > dc3;...
ColC                        | ColD | ColE              
----------------------------+------+----------------------------------------      
abcd-ppppp > e-ppt > efg-pp | 2016 | 2016abcd-ppppp > 2016e-ppt > 2016efg-pp
hij-aaa > kl-bbb > iiii-hhh | 2012 | 2012hij-aaa > 2012kl-bbb > 2012iiii-hhh           
aa-fff                      | 2017 | 2017aa-fff                       
a-pp > bbb-a                | 2014 | 2014a-pp > 2014bbb-a                  

如何在 T-SQL 中执行此操作？

Answer 1

Recursive CTE可能是

最适合您的方法

declare @t table (ColA varchar(100), ColB varchar(100), ColD int)
insert into @t values
    ('abcd > e > efg', 'ppppp > ppt > pp', 2016)
    , ('hij > kl > iiii', 'aaa > bbb > hhh', 2012)
    , ('aa', 'fff', 2017)
    , ('a > bbb', 'pp > a', 2014)
;with rcte as (
    select
        *, ColA2 = cast(ColA + ' >' as varchar(2000))
        , ColB2 = cast(ColB + ' >' as varchar(2000))
        , ca = charindex('>', ColA + ' >'), cb = charindex('>', ColB + ' >')
        , ColE = cast('' as varchar(4000))
    from 
        @t
    union all
    select
        ColA, ColB, ColD
        , cast(stuff(ColA2, 1, ca + 1, '') as varchar(2000))
        , cast(stuff(ColB2, 1, cb + 1, '') as varchar(2000))
        , charindex('>', stuff(ColA2, 1, ca + 1, '')), charindex('>', stuff(ColB2, 1, cb + 1, ''))
        , cast(ColE + ' > ' + cast(ColD as varchar(9)) + left(ColA2, ca - 2) + '-' + left(ColB2, cb - 2) as varchar(4000))
    from
        rcte
    where
        ca > 0
)
select 
    ColA, ColB, ColD, stuff(ColE, 1, 3, '')
from 
    rcte
where 
    ca = 0

输出

ColA             ColB               ColD    ColE
---------------------------------------------------------------------
a > bbb          pp > a             2014    2014a-pp > 2014bbb-a
aa               fff                2017    2017aa-fff
hij > kl > iiii  aaa > bbb > hhh    2012    2012hij-aaa > 2012kl-bbb > 2012iiii-hhh
abcd > e > efg   ppppp > ppt > pp   2016    2016abcd-ppppp > 2016e-ppt > 2016efg-pp