【SqlServer】字符串拆分需求实现

需求：将表中每个字符串中的字符拆解出来，放到一个新表的一列中
*相关数据已经脱敏处理

实现

if(object_id('tempdb..#t') is not null ) drop table #t
go 

select ROW_NUMBER() over(order by ID desc) ID,replace(display_name_cn,':','')  dname into #t
from testtable 
where 
--拆解字符串条件限制
replace(display_name_cn,':','') is not null /*非空*/
and replace(display_name_cn,':','') <>'' /*非空串*/;

--优化查询
create CLUSTERED index idx_t_id on #t(id);

--新建临时表
if(object_id('tempdb..#tb') is not null ) drop table #tb
go 
create table #tb (
id int,val nvarchar(1)
);

-------------------双循环插入数据
declare @i int=1,@j int=1,@k int=(select max(id) from #t);
SET NOCOUNT ON; 
while @i<=@k
begin
set @j=1
    while @j<=(select len(dname) from #t where id =@i)
    begin
    insert into #tb 
    select id,substring(dname,@j,1)
    from #t where id =@i;
    set @j=@j+1
    end
set @i=@i+1
end;
-------------------

--results
select * from #tb