Database Challenges

-- Narasimharao_kompella_tsqlchallenge_14.sql
DECLARE @t TABLE (Data VARCHAR(40) ) 
 

INSERT @t (Data) SELECT '9992EDC6-D117-4DEE-B410-4E5FAE46AE97' 
INSERT @t (Data) SELECT '0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1' 
INSERT @t (Data) SELECT '4A73E7EB-7777-4A04-9258-F1E75097977C' 
INSERT @t (Data) SELECT '5AAF477C-274D-400D-9067-035968F33B19' 
INSERT @t (Data) SELECT '725DA718-30D0-44A9-B36A-89F27CDFEEDE' 
INSERT @t (Data) SELECT '8083ED5A-D3B9-4694-BB04-F0B09C588888' ;
 
 
with   cte0 as (select 1 as c union all select 1),
        cte1 as (select 1 as c from cte0 a, cte0 b),
		cte2 as (select 1 as c from cte1 a, cte1 b),
		cte3 as (select 1 as c from cte2 a, cte2 b),
	    nums as (select row_number() over (order by c) as n from cte3) ,      

	Out as (SELECT a.Data, substring(a.Data,b.n,1) as 'Char', b.n as Pos, max(c.n-b.n+1) as 'Len' FROM @t a, Nums b, Nums c
	where b.n < datalength(a.Data)
	and substring(a.Data,b.n+1,1) = substring(a.Data,b.n,1)
	and (b.n = 1 or substring(a.Data,b.n-1,1) <> substring(a.Data,b.n,1))
	and c.n > b.n
	and substring(a.Data,b.n,c.n-b.n+1) not like '%[^' + substring(a.Data,b.n,1) + ']%'
	and b.n <= len(a.Data)
	and c.n <= len(a.Data)
	group by a.Data, b.n, substring(a.Data,b.n,1))

select a.* from Out a
order by (select max(Len) from Out where Data = a.Data) desc, a.Data, a.Pos

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.