Database Challenges

;WITH
cteNumbers AS ( -- our Number or "Tally" table source:
	SELECT TOP 40 ROW_NUMBER() OVER(ORDER BY object_id) AS n FROM master.sys.system_objects
	),
cteDoubles AS ( -- Find the beginnings of each repeated character sequence:
	SELECT 
		DATA,
		SUBSTRING(Data, N, 1)                AS [Char],
		SUBSTRING(Data, N, DATALENGTH(Data)) AS [Remainder],
		N AS [Pos]
	FROM @t, cteNumbers
	    -- Only those characters, where the next character is the same:
	WHERE SUBSTRING(Data, N, 1) = SUBSTRING(Data, N+1, 1)
	    -- but Not those where the previous character is also the same:
	AND 1 = CASE 
		WHEN N=1 THEN 1
		WHEN SUBSTRING(Data, N, 1) <> SUBSTRING(Data, N-1, 1) THEN 1
		ELSE 0 END
	AND N < DATALENGTH(Data)
	),
cteRuns AS (    -- Find the ends of the character runs:
	SELECT
		Data,
		Char,
		Pos,
		    -- find the next character in the GUID , that is different:
		CASE PATINDEX( '%[^'+Char+']%', Remainder)-1
			WHEN -1 THEN DATALENGTH(Remainder)
			ELSE PATINDEX( '%[^'+Char+']%', Remainder)-1 END AS [Len]
	FROM cteDoubles
	),
cteMaxRuns AS ( -- Accumulate the Max run length for each GUID:
	SELECT
		Data,
		Char,
		Pos,
		[Len],
		MAX([Len]) OVER(PARTITION BY Data) AS RecRunLen
	FROM cteRuns AS r1
	)
SELECT          -- Sort the rows and output them:
	Data,
	Char,
	Pos,
	[Len]
FROM cteMaxRuns
ORDER BY RecRunLen DESC, Data, 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.