create function dd(@s varchar(4000)) returns @t table(d varchar(10)) as begin declare @tmp varchar(8000) set @tmp = @s + ',' while (charindex(',',@tmp)>0) begin insert @t select left(@tmp ,charindex(',',@tmp)-1) set @tmp = right(@tmp ,len(@tmp) - charindex(',',@tmp)) end return end go select a.col1,b.d,a.col2 from (select 'fir'as col1,'last' as col2) a,dbo.dd('b,c') b drop function dd/* col1 d col2 ---- ---------- ---- fir b last fir c last */
returns @t table(d varchar(10))
as
begin
declare @tmp varchar(8000)
set @tmp = @s + ','
while (charindex(',',@tmp)>0)
begin
insert @t select left(@tmp ,charindex(',',@tmp)-1)
set @tmp = right(@tmp ,len(@tmp) - charindex(',',@tmp))
end
return
end
go
select a.col1,b.d,a.col2 from (select 'fir'as col1,'last' as col2) a,dbo.dd('b,c') b
drop function dd/*
col1 d col2
---- ---------- ----
fir b last
fir c last
*/