f_VersionCompare

BEGIN

    -- variables

    DECLARE delimLength INT(11) DEFAULT 1;

    DECLARE v1Length INT(11) DEFAULT 0;

    DECLARE v2Length INT(11) DEFAULT 0;

    DECLARE Result INT(11) DEFAULT 0;

    -- set delimiter length

    SET delimLength = LENGTH(delim);

    -- find the length of array indexes between each string, taking into account length of the delimiter

    SET v1Length = ((LENGTH(ver1) - LENGTH(REPLACE(ver1, delim, ''))) / delimLength)+1;

    SET v2Length = ((LENGTH(ver2) - LENGTH(REPLACE(ver2, delim, ''))) / delimLength)+1;

    SET @x = 1;

    REPEAT

        -- set result based on current array index; alpha is ignored

        IF CONVERT(REPLACE(SUBSTRING(SUBSTRING_INDEX(ver1, delim, @x), LENGTH(SUBSTRING_INDEX(ver1, delim, @x-1))+1), delim, ''), UNSIGNED INTEGER)

            > CONVERT(REPLACE(SUBSTRING(SUBSTRING_INDEX(ver2, delim, @x), LENGTH(SUBSTRING_INDEX(ver2, delim, @x-1))+1), delim, ''), UNSIGNED INTEGER)

            THEN SET Result = -1; 

        END IF;

        IF CONVERT(REPLACE(SUBSTRING(SUBSTRING_INDEX(ver1, delim, @x), LENGTH(SUBSTRING_INDEX(ver1, delim, @x-1))+1), delim, ''), UNSIGNED INTEGER)

            < CONVERT(REPLACE(SUBSTRING(SUBSTRING_INDEX(ver2, delim, @x), LENGTH(SUBSTRING_INDEX(ver2, delim, @x-1))+1), delim, ''), UNSIGNED INTEGER)

            THEN SET Result = 1; 

        END IF;

        SET @x = @x + 1;

    -- Do this until a result is discovered, or the shortest array is completed

    UNTIL Result <> 0 OR @x > v1Length OR @x > v2Length END REPEAT;

    -- if arrays at shortest indexes are equal, then longest index wins

    IF Result = 0 THEN

        SET Result = IF(v1Length > v2Length, -1, IF(v1Length < v2Length, 1, 0)); 

    END IF; 

    -- return result

    RETURN Result;

END