sql - MS Sql 按子字符串拆分特定列

Question

我正在尝试编写一个查询来按子字符串拆分和连接特定列。不知道如何实现

    Create table #tmp1
    (
    ID bigint,
    Code  varchar(10)
    )
    insert into #tmp1 values(1,'')
    insert into #tmp1 values(2,'DCBA')
    insert into #tmp1 values(3,'BACD')
    insert into #tmp1 values(4,'ABCD')
    select * from #tmp1
    select
    ID,
    * from #tmp1 a

我希望我的最终结果为

1   
2   DCBA    D   1
2   DCBA    C   2
2   DCBA    B   3
2   DCBA    A   4
3   BACD    B   1
3   BACD    A   2
3   BACD    C   3
3   BACD    D   4
4   ABCD    A   1        
4   ABCD    B   2    
4   ABCD    C   3 
4   ABCD    D   4 

我试过 sql substring 但无法完成

标签： sqlsql-serversplitsubstring

Answer 1

递归 CTE 可能是最简单的解决方案：

with cte as (
      select id, code, stuff(code, 1, 1, '') as rest, left(code, 1) as letter, 1 as lev
      from #tmp1
      union all
      select id, code, stuff(rest, 1, 1, '') as rest, left(rest, 1) as letter, lev + 1
      from cte
      where rest <> ''
     )
select id, code, letter, lev
from cte;

这是一个 db<>fiddle。

实际上，这里有一个稍微简单的版本：

with cte as (
      select id, code, left(code, 1) as letter, 1 as lev
      from tmp1
      union all
      select id, code, substring(code, lev, 1) as letter, lev + 1
      from cte
      where lev < len(code)
     )
select id, code, letter, lev
from cte;