if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_cTmp]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_cTmp]
Go
create function f_cTmp()
returns @a table(id int primary key)
as
begin
declare @b table(id int identity(1,1),name varchar(100))
insert @b select top 800 name from syscolumns
insert @a select id from @b
return
end
Go
--可实现行分离(注,调用此函数一定要先生成Table f_dd_a
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_dd]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_dd]
Go
create function dbo.f_dd(@Amount varchar(1000),@SplitChar varchar(10))
returns table
as
return(
select SubString(@Amount,M.id,Charindex(@SplitChar,@Amount+@SplitChar,M.id)-M.id) as aName
from (select id from dbo.f_cTmp()) as M--f_dd_a_001
where M.id<len(@Amount)+1 and Charindex(@SplitChar,@SplitChar+@Amount,M.id)-M.id=0
)
Go
select aName from dbo.f_dd('123|abc|xxx|ccc','|')
declare @s nvarchar(1000)
set @s='123|abc|xxx|ccc'
set @s='select * into ##t from( select '''+replace(@s,'|',''' as s union all select ''')+''''+')t select * from ##t '
print @s
exec(@s)