以下的文章主要是介绍的是SQL Server拆分字符串的3常用种方法，我前两天在相关网站看见SQL Server拆分字符串的3常用种方法的资料，觉得挺好，就拿出来供大家分享，望会给大家带来一些帮助在此方面。
use tempdb use tempdb go
测试数据
declare @s varchar(1000) set @s='ak47,mp5,1,23'
要求输出结果
S ak47 mp5 1 23
SQL Server拆分字符串的3种方法对比:
SQL Server拆分字符串1.[朴实]动态Exec方法:
declare @s1 varchar(1000) set @s1=right(replace(','+@s,',',''' as S union select '''),len(replace(','+@s,',',''' as S union select '''))-12)+'''' exec(@s1)
SQL Server拆分字符串2.[变通]表交叉方法:
select replace(reverse((left(s,charindex(',',s)))),',','') as S from( select r,reverse(left(@s,r))+',' as s from( select (select count(*) from sysobjects where name<=t.name ) as r from sysobjects t )a where r<=len(@s) and left(@s+',',r+1) like '%,' )t order by r
SQL Server拆分字符串3.[高级]XML方法:
DECLARE @idoc int; DECLARE @doc xml; set @doc=cast('<Root><item><S>'+replace(@s,',','</S></item><item><S>')+'</S></item></Root>' as xml) EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc SELECT * FROM OPENXML (@Idoc, '/Root/item',2) WITH ( [S] varchar(10) )
以上的相关内容就是对SQL Server拆分字符串的三种方法的介绍，望你能有所收获。