Database Challenges

/*
TSQL Challenge #14 Solution by Mike DeFehr

The main idea of my solution is to turn each [Data] 
into an indexed array (in the form of a table with 
one row for each character and a row number for the 
index), then sort the array by character then index 
and look for consecutive index values by applying 
another row number and comparing the differences 
between this row number and the array index - if 2 
or more of these differences are the same (within 
a distinct character), the characters must be 
adjacent.

A simple example:

Array data: AFBB04B
Index     : 1234567

now sort the array by character, apply a row number 
partitioned by character and take the difference:

Array data:					04ABBBF
Index:						5613472
New, partitioned Row Num:	1111231
Difference:					4502241

Grouping by Array data AND Difference, we see that 
there is only one small sequence of B's.  Note that 
the third "B" and the "0" happend to have the same 
index/rownum difference - this doesn't matter because 
we group by character first. the count becomes the
length of the sequence and the Minimum index value 
becomes the starting position

Grouped array data:			04ABBF
Difference:					450241
COUNT(*):					111211
MIN(Index)					561372
*/

SELECT	
	t.Data,
	c.[Char], 
	c.[Pos], 
	c.[Len]
FROM @t t
CROSS APPLY (	
	--the outer query groups by [Char] and [RNDiff] - within 
	--a given [Char] any [RNDiff] values that are different
	--means the characters are not consecutive - the HAVING 
	--clause filters out any singleton characters.
	SELECT	
		[Char], 
		MIN(RowNum) AS Pos, 
		COUNT(*) AS [Len]
	FROM (	
		--the inner query turns [Data] into an indexed array 
		--and applies a row number partitioned by
		--[Char] and ordered by the array index from which 
		--it subtracts the array index
		SELECT	
			SUBSTRING(t.Data,Number,1) AS [Char],
			ROW_NUMBER() OVER (
							PARTITION BY SUBSTRING(t.Data,Number,1) 
							ORDER BY Number
						) - Number AS RNDiff,
			Number AS RowNum
		FROM master..spt_values
		WHERE Number <= LEN(t.Data)
			AND number > 0
			AND [type] = 'P'
	) b
	GROUP BY [Char], RNDiff
	HAVING COUNT(*) > 1
) c
ORDER BY 
	MAX(c.[Len]) OVER (PARTITION BY t.Data) 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.