Database Challenges

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'



--use a numbers CTE to create a separate row for each char in the string
;WITH 
	 P0 AS (SELECT 1 AS C UNION ALL SELECT 1)
	,P1 AS (SELECT 1 AS C FROM P0 AS A, P0 AS B)
	,P2 AS (SELECT 1 AS C FROM P1 AS A, P1 AS B)
	,P3 AS (SELECT 1 AS C FROM P2 AS A, P2 AS B)
	,NumbersTable AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS n FROM P3) 

--Grouped Data CTE
,GroupedData AS
(
	SELECT
		 Data
		,SUBSTRING(Data,NumbersTable.n,1)			AS Chr
		,NumbersTable.n								AS Pos

		--dense rank minus the pos to create a grouping constant for ajacent equal values
		,(DENSE_RANK() OVER	
			(
				PARTITION BY 
					 Data
					,SUBSTRING(Data,NumbersTable.n,1)
				ORDER BY 
					 Data
					,SUBSTRING(Data,NumbersTable.n,1)
					,NumbersTable.n
			)-NumbersTable.n)						AS Grp			
	FROM
		 @t 
		,NumbersTable  
	WHERE
		NumbersTable.n <= LEN(Data)
)

--Summarized Data CTE
,SummarizedData AS
(
	SELECT
		 Data
		,Chr
		,MIN(Pos)		AS Pos
		,COUNT(Chr)		AS Len
	FROM
		GroupedData	
	GROUP BY
		 Data
		,Chr
		,Grp
	--this limits the output to record groups where ajacent values are equal
	HAVING
		COUNT(*) > 1
)

--output result
SELECT
	 SummarizedData.Data
	,SummarizedData.Chr
	,SummarizedData.Pos
	,SummarizedData.Len
FROM
	SummarizedData
	--apply largest group value by Data for ordering
	CROSS APPLY
		(
			SELECT
				 SummarizedDataApply.Data
				,MAX(SummarizedDataApply.Len) AS MAX_Len
			FROM
				SummarizedData SummarizedDataApply
			WHERE
				SummarizedData.Data = SummarizedDataApply.Data
			GROUP BY
				SummarizedDataApply.Data
		) AS MaxLenTable
ORDER BY
	 MaxLenTable.MAX_Len DESC
	,SummarizedData.Data
	,SummarizedData.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.