SQL Server秘籍：数据分隔解密，数据库处理新境界！

数据库干货铺 2023-11-27

283

点击上方蓝字关注我

在数据数据过程中经常会遇到数据按照一定字符进行拆分，而在不同版本的SQL SERVER数据库中由于包含的函数不同，处理的方式也不一样。本文将列举2个版本的数据库中不同的处理方法。

1. 使用 XML 方法

在SQL SERVER 2016版本之前，因为没有直接进行数据拆分的方法，因此需要通过其他方式来解决，以下是案例:

首先，我们考虑以下的案例。我们有一个临时表 #Tab，包含两列 Col1 和 Col2，其中 Col2 是逗号分隔的字符串。

-- 创建临时表
if object_id('tempdb..#Tab') is not null drop table #Tab;
if object_id('tempdb..#test') is not null drop table #test;
go


create table #Tab ([Col1] int, [COl2] nvarchar(5));
go


insert #Tab 
select 1, N'a,b,c' union all 
select 2, N'd,e' union all 
select 3, N'f';
go


-- 按照逗号拆分
select  
    a.COl1, b.Col2, row_number() over (partition by COl1 order by COl1) as ID 
into #test
from  
    (select Col1, COl2=convert(xml,' <root> <v>'+replace(COl2,',',' </v> <v>')+' </v> </root>') from #Tab) a 
outer apply 
    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v') C(v)) b;


-- 重新组合相同 COl1 值的数据
select  
    c.Col1, 
    case when COl2='' then NULL else LEFT(COl2, LEN(COl2)-1) end as COl2  
from (
    select Col1,
        (
            select COl2+','
            from #test 
            where COl1 = b.COl1 
            order by ID asc 
            for xml path('')
        ) as COl2
    from #test b  
    group by Col1
) c;


-- 结果展示
select * from #test;
select * from #Tab;


-- 清理临时表
if object_id('tempdb..#Tab') is not null drop table #Tab;
if object_id('tempdb..#test') is not null drop table #test;


复制

2. 使用 STRING_SPLIT 函数

现在，我们看一下第二种方法，使用 SQL Server 2016 及更新版本引入的 STRING_SPLIT 函数。

-- 创建临时表
if object_id('tempdb..#Tab') is not null drop table #Tab;
if object_id('tempdb..#test') is not null drop table #test;
go


create table #Tab ([Col1] int, [COl2] nvarchar(5));
go


insert #Tab 
select 1, N'a,b,c' union all 
select 2, N'd,e' union all 
select 3, N'f';
go


-- 使用 STRING_SPLIT 拆分数据
select  
    Col1, 
    value as Col2, 
    row_number() over (partition by Col1 order by Col1) as ID 
into #test
from #Tab
cross apply string_split(COl2, ',');


-- 重新组合相同 COl1 值的数据
select  
    Col1, 
    string_agg(Col2, ',') as Col2
from #test
group by Col1;


-- 结果展示
select * from #test;
select * from #Tab;


-- 清理临时表
if object_id('tempdb..#Tab') is not null drop table #Tab;
if object_id('tempdb..#test') is not null drop table #test;


复制

3. 小结

无论是使用 XML 数据方法，还是使用 STRING_SPLIT
函数，都可以有效地处理 SQL Server 中的逗号分隔值。选择哪种方法取决于你的数据库版本和个人偏好。希望这篇文章对你在 SQL Server 中处理逗号分隔值时有所帮助。如果有任何问题或疑问，请随时在评论中留言。感谢阅读！