Database Challenges

TSQL Challenge 14 - Solution by Mark Cowne

-- mark_cowne_tsqlchallenge_14.sql
SELECT 
	Data,
	SUBSTRING(Data,Number,1) AS [Char],
	Number AS Pos,
	PATINDEX(
		'%[^' + SUBSTRING(Data,Number,1) + ']%',
		SUBSTRING(Data, Number+1, LEN(Data)) + CHAR(10)
	) AS [Len]
FROM @t
INNER JOIN master.dbo.spt_values ON 
	Number BETWEEN 1 AND LEN(Data)-1 
	AND type='P'
	AND SUBSTRING(Data,Number,1)=SUBSTRING(Data,Number+1,1)
	AND (
		Number=1 
		OR 
		SUBSTRING(Data,Number,1)<>SUBSTRING(Data,Number-1,1)
	) 
ORDER BY 
	MAX(
		PATINDEX(
			'%[^'+SUBSTRING(Data,Number,1)+']%',
			SUBSTRING(Data,Number+1,LEN(Data))+CHAR(10))
		) 
	OVER(PARTITION BY 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.