Find the longest alphabetical sequence in strings.sql

SET NOCOUNT ON

----
---- Prereq:
---- Table with a unique unique_id field and a search_string field to do the sequential search on
---- In this demo, the table is called Source
----

--Holds vital information for processing as well as the results
DECLARE @details AS table
(
        unique_id nvarchar(100)
    ,   search_string varchar(100)
    ,   longestSequence varchar(100)
    ,   processed bit
)

--Fill up the table with all of the information
INSERT @details
(
        unique_id
    ,   search_string
    ,   longestSequence
    ,   processed
)
SELECT
        unique_id
    ,   search_string
    ,   NULL
    ,   0
FROM Source

--Holds the unique_id of the current record being processed
DECLARE @id nvarchar(100)

--Get the first unique_id that hasn't been processed
SET @id = (SELECT TOP 1 unique_id FROM @details WHERE processed = 0 ORDER BY unique_id)
--Loop through the ID's until there are no processed items left
WHILE @id IS NOT NULL
BEGIN
    --Hold the current search string
    DECLARE @ss varchar(100)
    SET @ss = (SELECT TOP 1 search_string FROM @details WHERE unique_id = @id)

    --Hold the current position that is being evaluated from in the search string
    DECLARE @pos int
    SET @pos = 1

    --Hold the various sequential strings in the substring. It is cleared out before @pos is incremented
    DECLARE @ssSequence AS table
    (
            character_id int
        ,   sequence varchar(100)
    )

    --Loop through each character in the search string
    WHILE @pos < LEN(@ss)
    BEGIN
        --Hold the current alphabetical sequence
        DECLARE @sequence varchar(100)

        --Hold the position of the substring search that starts at @pos and goes to the end of the search string
        DECLARE @subPos int
        SET @subPos = @pos

        --Hold the current character that's being evaluated
        DECLARE @c char
        SET @c = SUBSTRING(@ss, @pos, 1)

        --Start out the current sequence with the character that's being evaluated
        SET @sequence = @c

        --Evaluate each character in the substring up to the point where it's no longer sequential
        WHILE @subPos < LEN(@ss)
        BEGIN
            --Hold the next character in the substring
            DECLARE @c2 char

            --Store the current character and the next character being processed
            SET @c = SUBSTRING(@ss, @subPos, 1)
            SET @c2 = SUBSTRING(@ss, @subPos + 1, 1)

            --Check if the next character in the substring is sequential
            IF ASCII(@c2) = ASCII(@c) + 1
            BEGIN
                --If it is sequential, append the character to the sequence string
                SET @sequence = @sequence + @c2
            END
            ELSE
            BEGIN
                --It's not sequential, no need to evaluate further
                BREAK
            END

            --Increment the substring position
            SET @subPos = @subPos + 1
        END

        --The substring search is complete, insert the sequential string created in the last loop, if there is one
        IF LEN(@sequence) > 1
        BEGIN
            INSERT @ssSequence
            (
                    character_id
                ,   sequence
            ) VALUES
            (
                    @pos
                ,   @sequence
            )
        END

        --Go to the next starting position in the search string and evaluate from there
        SET @pos = @pos + 1
    END

    --Holds the longest sequential string for the current search string
    DECLARE @longestSequence varchar(50)
    SET @longestSequence = (SELECT TOP 1 sequence FROM @ssSequence WHERE ISNUMERIC(sequence) = 0 ORDER BY LEN(sequence) DESC)

    --Update the table with the longest string and the fact that it's been processed
    UPDATE @details SET longestSequence = @longestSequence, processed = 1 WHERE unique_id = @id

    --Clear the table that holds the search string sequences for the substring before going on to the next position in the search string
    DELETE @ssSequence

    --Get the next user_id that hasn't been processed
    SET @id = (SELECT TOP 1 unique_id FROM @details WHERE processed = 0 ORDER BY unique_id)
END

SELECT
        Source.unique_id
    ,   Source.search_string
    ,   ud.longestSequence
    ,   ISNULL(LEN(ud.longestSequence),0) AS length
FROM    Source
INNER JOIN @details AS ud ON (Source.unique_id = ud.unique_id)
ORDER BY
        LEN(ud.longestSequence) DESC
    ,   ud.longestSequence DESC
    ,   Source.unique_id ASC