需要调用自定义函数 SF_Split
if exists(select 1 from sysobjects where id=object_id('dbo.SP_Split') and xtype= 'P')
begin
drop PROCEDURE SP_Split
end
go
/*
created on 02/11/2012,jonse
调用例子如下：
SP_Split 'RA101118004!8!7,RA101118004!8!8,RA101118004!10!9',',','!'
执行结果为:
Short1 Short2 Short3
----------- ------- ---------
RA101118004 8 7
RA101118004 8 8
RA101118004 10 9
*/
create PROCEDURE SP_Split(@SourceSql varchar(8000),@StrSeprate varchar(1),@StrSeprate1 varchar(1))
--实现split功能 的函数
as
begin
if object_id('tempdb.dbo.#tmp1') is not null
drop table #tmp1 --删除临时表
select * into #tmp1 from dbo.[SF_Split](@SourceSql,@StrSeprate)
if exists(select 1 from #tmp1 where Short like '%'+@StrSeprate1+'%')
begin
declare @str2 varchar(50)=(select top 1 short from #tmp1)
declare @iColumnCount int=(select (len(@str2)-len(replace(@str2,@StrSeprate1,'')))/len(@StrSeprate1))+1
declare @sTableSql varchar(1000)='declare @temp2 table('
declare @sTableData varchar(max)=''
declare @iTmp int=1
while(@iTmp<=@iColumnCount)
begin
set @sTableSql+='Short' + CONVERT(varchar,@iTmp) +' varchar(50),'
set @iTmp+=1
end
select @sTableData +=' union all select '''+replace(Short,@StrSeprate1,''',''')+'''' from #tmp1
if(LEN(@sTableSql)>1)
begin
set @sTableSql=SUBSTRING(@sTableSql,1,len(@sTableSql)-1) +') '
set @sTableData=SUBSTRING(@sTableData,12,LEN(@sTableData)-11)
EXEC(@sTableSql + ' insert into @temp2 '+ @sTableData + ' select * from @temp2')
--print @sTableSql + ' insert into @temp2 '+ @sTableData + ' select * from @temp2'
end
end
end
