SQLServer拆分字符串的三种方法

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(',''' 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)

???????? )

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!