ms sql按子字符串拆分特定列

jecbmhm3 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(299)

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

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子字符串，但无法完成它

sql sql-server split substring

来源：https://stackoverflow.com/questions/63072139/ms-sql-splitting-specific-column-by-sub-string

1条答案

按热度按时间

vxbzzdmp1#

递归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;

这是一把小提琴。
实际上，这里有一个稍微简单的版本：

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;

赞(0）回复(0）举报 2021-07-24

我来回答

ms sql按子字符串拆分特定列

1条答案

相关问题

热门标签

最新问答