Database Challenges

-- leonid_koyfman_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 Numbers AS
( SELECT 
	NUMBER AS N
  FROM 
	MASTER..SPT_VALUES 
  WHERE 
	[TYPE]='P'
	AND NUMBER BETWEEN 1 AND 36
)
SELECT 
  Data
, [Char]
, Pos
, [Len]  
FROM 
  @t 
CROSS APPLY
  (
  SELECT 
	[CHAR]
  ,	MIN(N)	AS	POS
  ,	COUNT(*)AS	[LEN] 
  FROM
	(
	SELECT 
	  N
	, [CHAR]
	, N-ROW_NUMBER() OVER(PARTITION BY [CHAR] ORDER BY N) AS CharGrpN
	FROM 
		(
		SELECT 
			N
		,	SUBSTRING(Data, N ,1)	AS [CHAR]
		FROM
			Numbers	
		)AS D1
	)D2
  GROUP BY [CHAR],CharGrpN
  HAVING COUNT(*)>1
  )AS D3
ORDER BY 
  MAX([Len])OVER(PARTITION BY Data) desc
, COUNT(*)	OVER(PARTITION BY Data) desc
, Data
, Pos

--Data                                 Char Pos  Len 
-------------------------------------- ---- ---- ----
--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.