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