Version compare

Question

I have around 2000+ sites maintaining different version of an in-house application which runs Oracle11g.

A table has a parameter which maintains the versions. COl_1 with values like 5.2.4 or 6.86 or 7.2.0.1 etc. I need to compare two sites versions like (6.2.3 of site1 is greater than site2 which might be 6.8). The datatype is Varchar2.

What is the easiest way? Is there anything built in? Based on the result I have to execute few inserts & merges.

So, how to compare 5.2.4 > 6.2.1.4 in oracle sql query?

Thanks in advance.

Answer 1

You can split string containing versions into individul parts using the below query, then you can sort and compare records.

SELECT x,
       to_number(regexp_substr( x, '\d+', 1, 1)) As x_1,
       to_number(regexp_substr( x, '\d+', 1, 2)) As x_2,
       to_number(regexp_substr( x, '\d+', 1, 3)) As x_3,
       to_number(regexp_substr( x, '\d+', 1, 4)) As x_4,
       to_number(regexp_substr( x, '\d+', 1, 5)) As x_5,
       to_number(regexp_substr( x, '\d+', 1, 6)) As x_6
FROM table123
order by 
    2 nulls first,
    3 nulls first,
    4 nulls first,
    5 nulls first,
    6 nulls first,
    7 nulls first

Demo: http://sqlfiddle.com/#!4/60df0/4

|          X | X_1 | X_2 |    X_3 |    X_4 |    X_5 |    X_6 |
|------------|-----|-----|--------|--------|--------|--------|
|      1.1.1 |   1 |   1 |      1 | (null) | (null) | (null) |
|     1.1.15 |   1 |   1 |     15 | (null) | (null) | (null) |
|      2.7.1 |   2 |   7 |      1 | (null) | (null) | (null) |
|     2.7.10 |   2 |   7 |     10 | (null) | (null) | (null) |
|     3.1..1 |   3 |   1 |      1 | (null) | (null) | (null) |
|      4.1.1 |   4 |   1 |      1 | (null) | (null) | (null) |
|      6.4.2 |   6 |   4 |      2 | (null) | (null) | (null) |
|        9.1 |   9 |   1 | (null) | (null) | (null) | (null) |
|      9.1.2 |   9 |   1 |      2 | (null) | (null) | (null) |
|     9.1.10 |   9 |   1 |     10 | (null) | (null) | (null) |
| 10.1.1.2.4 |  10 |   1 |      1 |      2 |      4 | (null) |
|     15.1.3 |  15 |   1 |      3 | (null) | (null) | (null) |
|     21.1.1 |  21 |   1 |      1 | (null) | (null) | (null) |
|     23.1.2 |  23 |   1 |      2 | (null) | (null) | (null) |
|    23.1.10 |  23 |   1 |     10 | (null) | (null) | (null) |
|     30.1.1 |  30 |   1 |      1 | (null) | (null) | (null) |
|     31.1.1 |  31 |   1 |      1 | (null) | (null) | (null) |
|       41.1 |  41 |   1 | (null) | (null) | (null) | (null) |