Home

TSQL Challenge 14 - Solution by IBRAHIM EM


-- IBRAHIM_EM_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 CTE (original,latest, ch,num)
AS
(
	select 	Data,REPLACE(Data,left(Data,1),''),left(Data,1),LEN(Data) - LEN(REPLACE(Data,left(Data,1),'')) from @t 
	Union All
	select 	original,REPLACE(latest,left(latest,1),''),left(latest,1),LEN(latest) - LEN(REPLACE(latest,left(latest,1),'')) from CTE
	where LEN(latest)>0 
),

CTE1(data,string,repeat,pos,ch,length)
AS
(
	select	original,replace(original,replicate(ch,num),'*'),num,
			patindex('%'+replicate(ch,num)+'%',original),ch,len(replicate(ch,num)) from CTE where num>1 and ch<>'-'
	union all 
	select	data,replace(string,replicate(ch,repeat),'*'),repeat-1,patindex('%'+replicate(ch,repeat)+'%',string),ch,
			len(replicate(ch,repeat)) from CTE1
	where patindex('%'+replicate(ch,repeat)+'%',string)>=0  
)

select data,ch,pos,[Len] from 
(
	select data,max(length) over(partition by data)ran,ch,pos,length [Len] from CTE1 where pos>0and repeat>0
)x
order by ran desc,data,pos