Database Challenges

;with tab1 as
(
SELECT   b5.i+b4.i +b3.i +b2.i + b1.i + b0.i x
FROM(SELECT 0 i UNION ALL SELECT 1) b0
CROSS JOIN(SELECT 0 i UNION ALL SELECT 2) b1
CROSS JOIN(SELECT 0 i UNION ALL SELECT 4) b2
CROSS JOIN(SELECT 0 i UNION ALL SELECT 8) b3
CROSS JOIN(SELECT 0 i UNION ALL SELECT 16) b4
CROSS JOIN(SELECT 0 i UNION ALL SELECT 32) b5
where b5.i+b4.i +b3.i +b2.i + b1.i + b0.i between 1 and 36
)
,tab2 as
(
select *,substring(data,x,1) as col
from @t cross join tab1  
)
,tab3 as
(
select a.*,col1=a.x-row_number()over(partition by a.data,a.col order by a.x) from tab2 a left join tab2 b 
on a.data=b.data and a.col=b.col and a.x+1=b.x
where b.data is not null
)
,tab4 as
(select data,col,col1,min(x) as Pos,count(*)+1 as len
from tab3
group by data,col,col1
)
select data,col as char,Pos,len from tab4
order by max(len)over(partition by data) desc,data,pos,row_number()over(partition by data order  by len )

Did you understand how this solution work? If you find it difficult to understand, you can Request an Explanation or you can Write an explanation to help others better understand this.