I am using Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
I have a table as follows:
Table1:
Name Null Type
----------------- -------- -------------
NAME NOT NULL VARCHAR2(64)
VERSION NOT NULL VARCHAR2(64)
Table1
Name Version
---------------
A 1
B 12.1.0.2
B 8.2.1.2
B 12.0.0
C 11.1.2
C 11.01.05
I want the output as:
Name Version
---------------
A 1
B 12.1.0.2
C 11.01.05
Basically, I want to get the row for each name which have highest version. For this I am using the following query:
SELECT t1.NAME,
t1.VERSION
FROM TABLE1 t1
LEFT OUTER JOIN TABLE1 t2
on (t1.NAME = t2.NAME and t1.VERSION < t2.VERSION)
where t2.NAME is null
Now 't1.VERSION < t2.VERSION' only works in normal version cases but in cases such as:
B 12.1.0.2
B 8.2.1.2
It fails, I need a PL/SQL script to normalize the version strings and compare them for higher value.
Just wrote a MySQL user defined function to accomplish the task, you can easily port it to ORACLE PL/SQL.
DELIMITER $$
DROP FUNCTION IF EXISTS `VerCmp`$$
CREATE FUNCTION VerCmp (VerX VARCHAR(64), VerY VARCHAR(64), Delim CHAR(1))
RETURNS INT DETERMINISTIC
BEGIN
DECLARE idx INT UNSIGNED DEFAULT 1;
DECLARE xVer INT DEFAULT 0;
DECLARE yVer INT DEFAULT 0;
DECLARE xCount INT UNSIGNED DEFAULT 0;
DECLARE yCount INT UNSIGNED DEFAULT 0;
DECLARE counter INT UNSIGNED DEFAULT 0;
SET xCount = LENGTH(VerX) - LENGTH(REPLACE(VerX, Delim,'')) +1;
SET yCount = LENGTH(VerY) - LENGTH(REPLACE(VerY, Delim,'')) +1;
IF xCount > yCount THEN
SET counter = xCount;
ELSE
SET counter = yCount;
END IF;
WHILE (idx <= counter) DO
IF (xCount >= idx) THEN
SET xVer = SUBSTRING_INDEX(SUBSTRING_INDEX(VerX, Delim, idx), Delim, -1) +0;
ELSE
SET xVer =0;
END IF;
IF (yCount >= idx) THEN
SET yVer = SUBSTRING_INDEX(SUBSTRING_INDEX(VerY, Delim, idx), Delim, -1) +0;
ELSE
SET yVer = 0;
END IF;
IF (xVer > yVer) THEN
RETURN 1;
ELSEIF (xVer < yVer) THEN
RETURN -1;
END IF;
SET idx = idx +1;
END WHILE;
RETURN 0;
END$$;
DELIMITER ;
Few test that I ran:
select vercmp('5.2.4','5.2.5','.');
+------------------------------+
| vercmp('5.2.4','5.2.5','.') |
+------------------------------+
| -1 |
+------------------------------+
select vercmp('5.2.4','5.2.4','.');
+------------------------------+
| vercmp('5.2.4','5.2.4','.') |
+------------------------------+
| 0 |
+------------------------------+
select vercmp('5.2.4','5.2','.');
+----------------------------+
| vercmp('5.2.4','5.2','.') |
+----------------------------+
| 1 |
+----------------------------+
select vercmp('1,2,4','5,2',',');
+----------------------------+
| vercmp('1,2,4','5,2',',') |
+----------------------------+
| -1 |
+----------------------------+