Database Challenges

-- fabien_contaminard (1)_tsqlchallenge_14_v1.sql
-- Datas

DECLARE @t TABLE (Data VARCHAR(36));

INSERT INTO @t (Data) SELECT '9992EDC6-D117-4DEE-B410-4E5FAE46AE97';
INSERT INTO @t (Data) SELECT '0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1';
INSERT INTO @t (Data) SELECT '4A73E7EB-7777-4A04-9258-F1E75097977C';
INSERT INTO @t (Data) SELECT '5AAF477C-274D-400D-9067-035968F33B19';
INSERT INTO @t (Data) SELECT '725DA718-30D0-44A9-B36A-89F27CDFEEDE';
INSERT INTO @t (Data) SELECT '8083ED5A-D3B9-4694-BB04-F0B09C588888';

-- Query

-- I'm using a recursive CTE to go throught every character of the input string in an ordered way.

WITH Decomp (Data, [Char], Pos, Strt, [Len]) AS
(
-- I'm initializing the R-CTE with the first character
SELECT T.Data, SUBSTRING(T.Data, 1, 1), 1, 1, 1
  FROM @t AS T
UNION ALL
SELECT D.Data, SUBSTRING(D.Data, D.Pos + 1, 1), D.Pos + 1,
  -- If my current character is the same than the previous one, then I keep the start position number
  -- else I'm reinitializing it to the current position number
       CASE SUBSTRING(D.Data, D.Pos + 1, 1) WHEN D.[Char] THEN D.Strt      ELSE D.Pos + 1 END,
  -- If my current character is the same than the previous one, then I'm incrementing the length of the concurrent characters
  -- else I'm reinitializing it to 1
       CASE SUBSTRING(D.Data, D.Pos + 1, 1) WHEN D.[Char] THEN D.[Len] + 1 ELSE 1         END
  -- I've got every information I need inside the RCTE, I don't need any join
  FROM Decomp AS D
  -- I have to stop at the end of the string
 WHERE D.Pos < LEN(D.Data)
 )
   SELECT Data, [Char], Strt AS Pos,
 -- As I've got one row for every concurrent characters, I keep the biggest length by data, character and starting position
          MAX([Len]) AS [Len]
     FROM Decomp
 -- I'm interested only where we have concurrent characters, or a length strictly superior to one
    WHERE [Len] > 1
 GROUP BY Data, [Char], Strt
 -- I'm using a max analytic function on the length, partitioned by the data to retrieve the asked order
 -- Analytic function being computed on the result set it is ok and it's not a nested aggregate
 ORDER BY MAX(MAX([Len])) OVER(PARTITION BY Data) DESC,
          Data ASC,
          Pos ASC;

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.