Database Challenges

-- dave_ballantyne_14.sql
-- CAVEAT : Will not work with strings > 36 Characters. Shorter strings sould be fine but untested
-- NOTES  : For a simple example of the Grp calculation in the cte cteCharList , pls see
--          my blog at http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/08/27/sql-and-c...
  

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 n1(n)
as
(
    Select 1 union all 
    Select 2 union all 
    Select 3 union all 
    Select 4 union all 
    Select 5 union all 
    Select 6
),
num(n)
as
(
    Select row_number() over (order by (select 0))
      from n1 cross join n1 as n2
)
,cteCharList(Data,Pos,Char,Grp)
as
(

  Select Data, n,substring(Data, n,1),
         n + row_number() over(partition by data order by substring(Data, n,1) ,n desc)
    from @t Cross join num
  
)
,
cteGrouping(Data,Char,StartPos,Len)
as
(
   select Data,Char,min(Pos),(max(Pos) - min(Pos))+1
   from cteCharList    
   group by data,grp,char
   having count(*) > 1
),
cteMaxLen(Data,MaxLen)
as
( 
   Select Data,max(Len)
    from  cteGrouping
   group by Data
)
select cteGrouping.Data,Char,Pos = StartPos,Len
  from cteGrouping
  join cteMaxLen
    on cteGrouping.Data = cteMaxLen.Data 
order by MaxLen desc,Data,StartPos
go

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.