Database Challenges

--- wim_buyens_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
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5),
  Numbers AS				--we create a table with numbers from 1 to 40
  (SELECT n FROM Nums WHERE n < 40
  ),
  Chars AS					--we get all Charater Position with same char right to it and not left to it!
  (SELECT n [Pos], SUBSTRING([Data],n,1) [Char],[Data] 
	FROM Numbers	n
	INNER JOIN @t t ON n< LEN([Data])
	WHERE EXISTS
	(SELECT * FROM @t t2 WHERE t.[Data]=t2.[Data] AND SUBSTRING(t2.[Data],n+1,1)=SUBSTRING(t.[Data],n,1))
	AND NOT EXISTS
	(SELECT * FROM @t t2 WHERE t.[Data]=t2.[Data] AND SUBSTRING(t2.[Data],n-1,1)=SUBSTRING(t.[Data],n,1))
  ),
  CharGroups AS		--we get all charactergroups
  (SELECT [Pos],[Pos] AS [CurrentPos],[Char],[Data],1 AS [Len] FROM Chars
	UNION ALL
  SELECT r.[Pos],r.[CurrentPos]+1,r.[Char],r.[Data],r.[Len]+1
  FROM Chars t
  INNER JOIN CharGroups r
		ON t.[Data]=r.[Data] and t.[Char]=r.[Char] AND t.[Pos]=r.[Pos]
		AND SUBSTRING(t.[Data],[CurrentPos]+1,1)=r.[Char]
  ),
  NotSortedResult as  --take for each startposition the max(length) to get to biggest group
  (SELECT [Data],[Char],Pos,MAX([len]) len FROM CharGroups
	GROUP BY [Data],[Char],Pos
   )
SELECT [Data],[Char],[Pos],[Len]
FROM NotSortedResult
ORDER BY MAX(LEN) OVER(PARTITION BY [Data] ) DESC,[Data] ASC,[Pos] ASC

/*result:
8083ED5A-D3B9-4694-BB04-F0B09C588888	B	20	2
8083ED5A-D3B9-4694-BB04-F0B09C588888	8	32	5
4A73E7EB-7777-4A04-9258-F1E75097977C	7	10	4
4A73E7EB-7777-4A04-9258-F1E75097977C	7	34	2
9992EDC6-D117-4DEE-B410-4E5FAE46AE97	9	1	3
9992EDC6-D117-4DEE-B410-4E5FAE46AE97	1	11	2
9992EDC6-D117-4DEE-B410-4E5FAE46AE97	E	17	2
5AAF477C-274D-400D-9067-035968F33B19	A	2	2
5AAF477C-274D-400D-9067-035968F33B19	7	6	2
5AAF477C-274D-400D-9067-035968F33B19	0	16	2
5AAF477C-274D-400D-9067-035968F33B19	3	32	2
725DA718-30D0-44A9-B36A-89F27CDFEEDE	4	15	2
725DA718-30D0-44A9-B36A-89F27CDFEEDE	E	33	2
*/

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.