Home

TSQL Challenge 14 - Solution by Celal Hakyemez


-- celal_hakyemez_tsqlchallenge_14
/* 
	TSQL Challenge 14
	Sql Server Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) 
*/
SET NOCOUNT ON
DECLARE @t TABLE ([Data] VARCHAR(40));
INSERT	@t VALUES ('9992EDC6-D117-4DEE-B410-4E5FAE46AE97'),('0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1'),('4A73E7EB-7777-4A04-9258-F1E75097977C'),
				  ('5AAF477C-274D-400D-9067-035968F33B19'),('725DA718-30D0-44A9-B36A-89F27CDFEEDE'),('8083ED5A-D3B9-4694-BB04-F0B09C588888');

DECLARE @charsets TABLE ([Char] VARCHAR(1));
DECLARE @partitions TABLE ([Start] TINYINT, [Size] TINYINT)


INSERT	@charsets VALUES('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('A'),('B'),('C'),('D'),('E'),('F');
INSERT	@partitions VALUES(0,8),(9,4),(14,4),(19,4),(24,12);

DECLARE @n INT = 0, @alphabet VARCHAR(17) = '0123456789ABCDEF-';

WITH cte AS (
SELECT	[Data], 
		[Char], 
		[Pos] = CHARINDEX([Char] + [Char], SUBSTRING([Data], [Start] + 1, [Size])) + [Start],
		[Len] = PATINDEX('%['+REPLACE(@alphabet,[Char],'')+']%', SUBSTRING([Data], CHARINDEX([Char] + [Char], SUBSTRING([Data], [Start] + 1, [Size])) + [Start], LEN([Data]) - CHARINDEX([Char] + [Char], SUBSTRING([Data], [Start] + 1, [Size])) + [Start] + 1) + '-') - 1
FROM	@t, @charsets, @partitions
WHERE	 CHARINDEX([Char] + [Char], SUBSTRING([Data], [Start] + 1, [Size])) > 0
)

SELECT	*
FROM	cte
ORDER BY MAX([Len]) OVER(PARTITION BY [Data]) DESC, [Data] ASC, [Pos] ASC