Oracle DB version column sorting

Question

I have Version_ref_no column having values like

    4.0.0.4
    4.0.0.2
    4.0.0.1
    4.0.0.8
    4.0.0.14 
    4.0.0.23 
    4.0.1.0

Is there any way in Oracle to sort it so that OP should be

   4.0.1.0
   4.0.0.23
   4.0.0.14
   4.0.0.8 
   4.0.0.4 
   4.0.0.2 
   4.0.0.1

You'll have to 1) splitt the string along the delimiter '.', 2) convert the parts to number and 3) sort on the separated number columns. If the number of substrings is constant, this is a nice homework;) — Marmite Bomber, Feb 23 '18 at 10:47

score 1 · Accepted Answer · edited Feb 23 '18 at 11:00

1

 SELECT * FROM YOUR_TABLE
 ORDER BY 
 to_number(regexp_substr(COL1, '[^.]+', 1, 1)) DESC NULLS FIRST,
 to_number(regexp_substr(COL1, '[^.]+', 1, 2)) DESC NULLS FIRST ,
 to_number(regexp_substr(COL1, '[^.]+', 1, 3)) DESC NULLS FIRST ,
 to_number(regexp_substr(COL1, '[^.]+', 1, 4)) DESC NULLS FIRST ;

edited Feb 23 '18 at 11:00

answered Feb 23 '18 at 10:51

venkatesh

151
13

welcome anyway!! don't mention thanks just up vote it ... – venkatesh Feb 23 '18 at 11:06
1

thanks for saving me the time of writing this answer :) – thatjeffsmith Feb 23 '18 at 14:03
Welcome @ thatjeffsmith – venkatesh Feb 26 '18 at 04:58

Oracle DB version column sorting

1 Answers1