-- Carl_Federl_S2V1_TSQLChallange_14.sql
-- tsql challenge 14: identify the longest sequence of characters in a string
-- Solution by Carl Federl
-- email cfederl AT yahoo.com
-- Solution 2
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
SET NOCOUNT ON
GO
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'
SET STATISTICS TIME ON
;WITH Tally ( N ) AS
(SELECT spt_values.number
FROM master.dbo.spt_values AS spt_values
WHERE spt_values.TYPE = 'P'
-- column "Data" maximum possibile length less the last character
AND spt_values.number BETWEEN 1 AND 39
)
, DataChars AS
(SELECT DATA
, Tally.N AS POSITION
, SUBSTRING( DATA, Tally.N , 1 ) AS MyChar
FROM @t
JOIN Tally
ON Tally.N BETWEEN 1 AND DATALENGTH(DATA)
)
, DataGroup AS
(SELECT DataChars.DATA
, DataChars.MyChar
, DataChars.POSITION
, COUNT(Nexts.MyChar ) AS Cnt
FROM DataChars
LEFT OUTER JOIN DataChars AS Nexts
ON Nexts.DATA = DataChars.DATA
AND Nexts.POSITION <= DataChars.POSITION
AND Nexts.MyChar <> DataChars.MyChar
GROUP BY DataChars.DATA
, DataChars.MyChar
, DataChars.POSITION
)
SELECT DataGroup.DATA
, DataGroup.MyChar as [Char]
, MIN(DataGroup.POSITION) AS Pos
, MAX(DataGroup.POSITION) - MIN(DataGroup.POSITION) + 1 AS [Len]
FROM DataGroup
GROUP BY DataGroup.DATA
, DataGroup.MyChar
, DataGroup.Cnt
HAVING MAX(DataGroup.POSITION) > MIN(DataGroup.POSITION)
ORDER BY DataGroup.DATA
, DataGroup.MyChar
, DataGroup.Cnt
GO
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
GO
