SQLServer拆分字符串的三种方法

核心提示：use tempdbgo--测试数据declare @s varchar(1000)set @s='ak47,mp5,1,23'/*要求输出结果Sak47mp5123*/--3种方法对比:--1.[朴实]动态Exec方法:declare @s1 varchar(1000)set @s1=right(re

use tempdb

go

--测试数据

declare @s varchar(1000)
set @s='ak47,mp5,1,23'

/*要求输出结果

S
----
ak47
mp5
1
23
*/

--3种方法对比:

--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)

--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

--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)
　　　　 )