How to compare 2 version strings (of the format x.x.x.x where x : {0, 99})
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production,
PL/SQL Release 11.2.0.2.0 - Production
I have a scenario like:
Table1:
Name Null Type
----------------- -------- -------------
NAME NOT NULL VARCHAR2(64)
VERSION NOT NULL VARCHAR2(64)
Data:
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 rows for each name which having highest version. For this i am using 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, So i want a PL/SQL script to normalize the version strings and compare them for higher value.