sql - 如何使用 SQL Server 将“01020304”更改为“01,02,03,04”？

Question

给定以下 SQL，我如何将值@x从01020304to更改01,02,03,04？

declare @x varchar(256)

set @x = '01020304'

--
-- How to change '01020304' to '01,02,03,04'?
--
select @x

标签： sqlsql-servertsqlsplit

Answer 1

一种方法是递归 CTE：

with cte as (
      select convert(varchar(max), '01020304') as rest, convert(varchar(max), '') as new_str, 1 as lev
      union all
      select stuff(rest, 1, 2, '') as rest, concat(new_str, ',', left(rest, 2)), lev + 1
      from cte
      where rest > ''
     )
select top (1) stuff(new_str, 1, 1, '')
from cte
order by lev desc;

这是一个 db<>fiddle。

如果字符串可能有超过 100 个元素，您将需要添加option (maxrecursive 0).