[SOLVED] Is it possible to order by substrings in postgreSQL?

Is it possible to order by substrings in postgreSQL?

I have a table called widgets that has a string column named version.

Data for version is in period-separated string format, similar to semantic versioning. e.g. "1.2.4"

When I do the traditional ORDER BY widgets.version then I get the following order

+--------------+
|    Widgets   |
+----+---------+
| id | version |
|----|---------|
| 1  | 1.3.2   |  <- This is fine
| 3  | 10.1.2  |  <- This should be last, since 10 > 4
| 2  | 4.5.7   |  <- This should be second, since 4 < 10
+----+---------+

How can I update my query so that the order returned is by version pt 1, version pt 2, then version pt 3?

Solution

The easiest way is to convert the version string to an integer array and then sort on that array:

select id, 
       version
from widgets
order by string_to_array(version, '.')::int[]

Note that this will fail if the version contains non-numeric values.