Sorting version numbers

We already know that every product has some version numbers usually in combination of dots eg. 1.1 or 1.2.1 etc . If we have these number stored in a table, then we can not simply sort them by issuing ORDER BY Clause. Here we will see how to sort them in ascending or descending order.

CREATE TABLE version_list
(
ID NUMBER(10),
VERSION VARCHAR2(30)
);


INSERT INTO version_list VALUES
(1, ‘10.2.3’);

INSERT INTO version_list VALUES
(2, ‘9.1.2’);

INSERT INTO version_list VALUES
(3, ‘10.1.2’);

SELECT *
FROM version_list
ORDER BY version;
10.2.3
9.1.2
10.1.2
You can see that the output is not sorted.

SELECT *
FROM version_list
ORDER BY TO_NUMBER(REPLACE(version, '', ''));
9.1.2
10.1.2
10.2.3
Now you can see that the output is sorted. We had strip all dots from the version_list and then issued ORDER BY and it worked.