Link to home
Create AccountLog in
Avatar of Blowfelt82
Blowfelt82

asked on

Order version numbers in Oracle.

In Oracle SQL how can I sort by version numbers, at the moment version numbers are as follows when I do a simple order by of the version column...

6.0.1
6.0.10
6.0.11
6.0.12
6.0.13
6.0.14
6.0.2
6.0.3

The list should be as follows...

6.0.1
6.0.2
6.0.3
6.0.10
6.0.11
6.0.12
6.0.13
6.0.14
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

just do a conversion to a number and all should be fine

order by to_number(your_column)
you need to ensure that no characters etc are there in that version number column otherwise you will get an error saying invalid number when you use to_number(..) conversion.
ASKER CERTIFIED SOLUTION
Avatar of OP_Zaharin
OP_Zaharin
Flag of Malaysia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Blowfelt82
Blowfelt82

ASKER

Worked like a charm - thanks
glad able to help :)
:)

interesting question, I was thinking of regexp_ replace for this, but I hate regular expressions to be honest so I went looking and:
a different approach is recorded here