以下的文章主要是介绍的是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拆分字符串的三种方法的介绍，望你能有所收获。
【编辑推荐】
