SQL 字段批量拆分

 

declare @t table (id int , names varchar (20 ))
insert into @t select 1 , 'jinjazz,blog,csdn'
insert into @t select 2 , 'sql,ms'

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

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