The Casons

Home Page

Baby boy

Hello. We are just some of the Casons in this world.  We created this special place on the Web to share random thoughts and code with you. We hope you enjoy what little content is here.

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 
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; 

Our new family

Website Builder