NOTE: This site is for testing only.
Thanks for visiting!
Using Common Table Expressions in SQL Server: Joins and Splits
/*sql server 2008 CTE split and join fun*/
/*split begins*/
if object_id(N'tempdb..#split') is not null drop table #split;
if object_id(N'tempdb..#joined') is not null drop table #joined;
declare @fun varchar(64) = 'The quick brown fox jumped over the lazy dogs!';
declare @delim char(1) = ' ';
select @fun as [Fun];
with split(i, token, remainder) as
(select 1
, left(@fun,charindex(@delim,@fun)-1)
, LTRIM(right(@fun,len(@fun)-CHARINDEX(@delim,@fun)))
union all
select i + 1
,case when charindex(@delim,remainder) > 0 then
left(remainder,charindex(@delim,remainder)-1)
else remainder end as token
,LTRIM(right(remainder,len(remainder)-CHARINDEX(' ',remainder))) as remainder
from split
where charindex(@delim,remainder) >= 0 and token != remainder
)
select i, token, remainder
into #split
from split;
select * from #split;
/*join begins*/
with joined (i, newfun, token) as (
select i, convert(varchar(max),token), token
from #split where i = 1
union all
select s.i, j.newfun + @delim + s.token, s.token
from joined j
inner join
#split s
on s.i = j.i + 1
)
select *
into #joined
from joined;
select * from #joined;