Database Challenges

-- aurelien_verla_tsqlchallenge_14_2

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 RESULTS ([D], [P], [C], [L]) AS (
	SELECT 
		[D], 
		MIN([P]), 
		[C], 
		MAX([P]) - MIN([P]) + 1
	FROM (
		SELECT
			((ROW_NUMBER() OVER(PARTITION BY T2.[Data] ORDER BY T2.[Data], T1.[Number]) + 1) / 2) AS 'G',
			T2.[Data] AS 'D',
			T1.[number] AS 'P',
			SUBSTRING(T2.[Data], T1.[number], 1) AS 'C'
		FROM [master].[dbo].[spt_values] AS T1, @t AS T2
		WHERE T1.[type] = 'P' AND T1.[number] BETWEEN 1 AND LEN(T2.[Data])
		AND (
				(
					SUBSTRING(T2.[Data], T1.[number], 1) <> SUBSTRING(T2.[Data], T1.[number] - 1, 1) 
					AND SUBSTRING(T2.[Data], T1.[number], 1) = SUBSTRING(T2.[Data], T1.[number] + 1, 1)
				)
			OR	(
					SUBSTRING(T2.[Data], T1.[number], 1) = SUBSTRING(T2.[Data], T1.[number] - 1, 1) 
					AND SUBSTRING(T2.[Data], T1.[number], 1) <> SUBSTRING(T2.[Data], T1.[number] + 1, 1)
				)
		)
	) AS T
	GROUP BY [D], [G], [C]
), ORDERS ([D], [L], [C]) AS (
	SELECT
		[D],
		MAX([L]),
		COUNT([D])
	FROM [RESULTS]
	GROUP BY [D]
)
SELECT
	T1.[D] AS 'Data',
	T1.[C] AS 'Char',
	T1.[P] AS 'Pos',
	T1.[L] AS 'Len'
FROM [RESULTS] AS T1, [ORDERS] AS T2
WHERE T1.[D] = T2.[D]
ORDER BY T2.[L] DESC, T2.[C] DESC, T1.[D], T1.[P]

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.