labtech Database

Parameters

Name	Type	Mode
ver1	varchar(255)	IN
ver2	varchar(255)	IN
delim	varchar(16)	IN

Definition

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

f_VersionCompare

Parameters

Definition