order by on text/number strings
I would like to sort the values from a column when the column value contains numeric and text strings but not sure how to achieve it. I've tried various lpad and substr orders/selects but not getting there.
I have values in 4 columns like:
Oracle Database 10g 10.2.0.4.0 /oem/oracle/product/10.2.0.4_Jul2010 p5-f4mp3
Oracle Database 10g 10.2.0.4.0 /oem/oracle/product/10.2.0.4_Oct2009 p5-f4mp3
Oracle Database 10g 10.2.0.4.0 /oem/oracle/product/10.2.0.4_Jul2008 p5-f15mp11
Oracle Database 10g 10.1.0.4.0 /oem/oracle/product/10.1 p5-f3mp14
Oracle Database 10g 10.2.0.4.0 /oem/oracle/product/10.2.3_av p5-f15mp9
Oracle Database 11g 11.2.0.1.0 /oem/oracle/product/11.2 p5-f16mp17
Oracle Database 11g 11.2.0.1.0 /oem/oracle/product/11.2.0.1_P_OIM_Oct2010 slglscad.internal.standardlife.com
Oracle Database 11g 11.2.0.1.0 /oem/oracle/product/11.2.0.1_P_Aud_Oct2010 slglscad.internal.standardlife.com
Oracle Database 11g 11.1.0.7.0 /oem/oracle/product/11.1.0.7_Apr2010 p5-f4mp22
Oracle8i Server 8.1.7.4.0 /oem/oracle/product/8.1.7 csm-node284
Oracle9i 9.2.0.8.0 /oem/oracle/product/9.2.0.8oct2009 csm-node284
Oracle9i 9.2.0.8.0 /oem/oracle/product/9.2.0.8_Jul2010 csm-node284
Oracle9i 9.2.0.8.0 /oem/oracle/product/9.2.0.8oct2009 csm-node284
Oracle9i 9.2.0.5.0 /oem/oracle/product/9.2.0 csm-node284
Oracle9i 9.2.0.8.0 /oem/oracle/product/9.2.0.8 p5-f4mp3
Oracle9i 9.2.0.8.0 /oem/oracle/product/9.2.0.8_Jul2008 csm-node284
Basically I want to order by the version number eg 8.1.7.4.0 then 9.2.0.5.0 etc and within that order by the home file path to produce an ordered list not only by version but also by month/year of the CPU patch.
Any help will be appreciated
Thanks
Mike