SQLServer字符串批量拆分得方法

其实拆分和批量拆分的方法是一样的，所谓批量就是用out apply来调用单个拆分函数。
 /*


 SQLServer字符串拆分函数,by jinjazz


 

 --原始数据


 id          names


 ----------- --------------------


 1           jinjazz,blog,csdn


 2           sql,ms


 

 --生成的数据


 id          rn          name


 ----------- ----------- ----------


 1           1           jinjazz


 1           2           blog


 1           3           csdn


 2           1           sql


 2           2           ms


 

 

 */


 

 set
 nocount
 on


 use
 tempdb


 go


 if
(object_id
('f_test'
) is
 not
 null
)

     drop
 function
 f_test


 go


 create
 function
 f_test
 (@a
 varchar
(max
))

 returns
 @t
 table
(rn
 int
,
v
 varchar
(max
)) 

 as


 begin


     insert
 into
 @t


     select
 b.
*
 from
(

     select
 convert
(xml
,
'<v>'
+
replace
 (@a
,
','
,
'</v><v>'
)+
'</v>'
) as
 f
)a


     outer
 apply


     (

         SELECT
 rn
=
row_number
()over
(order
 by
 getdate
()),
 t
.
c
.
value
('.'
,
'varchar(max)'
) AS
 f


                  FROM
 a
.
f
.
nodes
('//v'
) AS
 t
(c
)

     )b    

     return
 

 end


 go


 

 

 declare
 @t
 table
(id
 int
,
names
 varchar
(20
))

 insert
 into
 @t
 select
 1
,
'jinjazz,blog,csdn'


 insert
 into
 @t
 select
 2
,
'sql,ms'


 

 select
 *
 from
 @t


 select
 a
.
id
,
rn
,
b.
v
 as
 name
 from
 @t
 a
 outer
 apply
 dbo
.
f_test
(a
.
names
) b

 

 set
 nocount
 off