What does one do on a Friday night after going out with some friends and having a couple of mugs of suds?  How about doing a T-SQL puzzle?

Jacob Sebastian and a team of MVPs and high-powered database gurus are running a series of challenges.  It’s the closest thing to getting an assignment in college.  The challenges serve no purpose other than to hurt your head and make you think through a problem in a variety of ways.

The winners are chosen based on a variety of parameters such as #reads and writes, the execution plan, etc.  The prize is braggers rights… Sorry, no checks.

Anyway, I took a stab at #14, which is the first time I paid any attention to this.  I hope this isn’t addictive because it’s 3AM on a Saturday right now and I’ve got stuff to do tomorrow.

For a complete description of challenge #14 please go to http://beyondrelational.com/blogs/tc/archive/2009/09/28/tsql-challenge-14-identify-the-longest-sequence-of-characters-in-a-string.aspx.  Those guys did a much better job than me describing the problem.

In general, for a given series of input strings that are stored in a table variable, determine the starting position and length of all of the consecutive series of single characters, and print the results.

I’m posting the results to my blog knowing that someone just might be tempted to snatch my solution and improve it.  It a compact piece of code, using a recursive CTE.  I figure if someone can take my stuff and improve it, all the power to you.  Besides, lets face it, my blog isn’t even known out there.

Anyway, here’s the code:

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';

-- Algorithm:    Use a recursive CTE to pop off the last character of a string.
--                Along the way determine sequences of characters and their length.
--                Order the output by the strings with the highest sequences DESC.

WITH string_cte (    [Data_full],            -- Carry the original string 
                    [Data],                    -- This sting will be shortened at the end
                    [Char],                    -- Pull off the last character from the string
                    [Pos],                    -- Position of the character
                    [Length],                -- What is the length of consecutive characters?
                    [Cluster_ID] ) AS        -- Used to identify clusters of consecutive characters
(    SELECT    t.[Data],
            t.[Data],
            SUBSTRING(t.[Data], LEN(t.[DATA]), 1) AS [Char],
            LEN(t.[DATA]) AS [Pos],
            1 AS [Length],
            1 AS [Cluster_ID]
    FROM  @t t

    UNION ALL

    SELECT    cte.[Data_full],
            SUBSTRING(cte.[Data], 1, LEN(cte.[Data]) - 1),
            SUBSTRING(cte.[Data], LEN(cte.[Data]) - 1, 1) AS [Char],
            [Pos] - 1,
            CASE
                WHEN SUBSTRING(cte.[Data], LEN(cte.[Data]), 1) = SUBSTRING(cte.[Data], LEN(cte.[Data]) - 1, 1)
                    THEN [Length] + 1
                ELSE 1
            END AS [Length],
            CASE
                WHEN SUBSTRING(cte.[Data], LEN(cte.[Data]), 1) = SUBSTRING(cte.[Data], LEN(cte.[Data]) - 1, 1)
                    THEN [Cluster_ID]
                ELSE [Cluster_ID] + 1
            END AS [Cluster_ID]
    FROM        string_cte cte
    WHERE LEN(cte.[Data]) > 1 )

SELECT    cte.[Data_full],
        cte.[Char],
        MIN(cte.[Pos]) AS [Pos],
        MAX(cte.[Length]) AS [Len]
FROM        string_cte AS cte
        -- This derived table is used for one purpose: 
        -- To rank the full string by the max # of consecutive characters
        INNER JOIN (SELECT    [Data_full],
                            MAX([Length]) AS sort_order
                    FROM        string_cte
                    GROUP BY [Data_full] ) d
            ON    d.[Data_full] = cte.[Data_full]
WHERE    cte.[Length] > 1
GROUP BY cte.[Data_full],
        cte.[Char],
        cte.[Cluster_ID],
        d.sort_order
ORDER BY d.sort_order DESC,
    cte.data_full ASC,
    MIN(cte.[Pos]) ASC;

And here’s the output:

I’ll look forward to the winning solutions to see how they did it.  The only way to really learn from these challenges is to do or at least attempt to do the challenge.  Without doing the actual work it’s like looking at the solutions to the NY Times crossword puzzle…  It’s easy when you know the answer.

Thanks to the creative and energetic souls who are hosting this challenge.

Happy SQL-ing!