How to sort "version" strings with SQL in Snowflake?

Question

I have a column with strings representing release "versions", like:

5.14.0
5.10.1
5.1.8
5.1.87
5.1.357
26.3.0
24.1.1

How can I sort these in Snowflake? Note that the numeric sort (what we want) is different than the simple string sort.

Answer 1

Using SPLIT_PART:

SELECT *
FROM data
ORDER BY SPLIT_PART(version_number, '.', 1)::INT,
         SPLIT_PART(version_number, '.', 2)::INT,
         SPLIT_PART(version_number, '.', 3)::INT;

Output:

VERSION_NUMBER
5.1.8
5.1.87
5.1.357
5.10.1
5.14.0
24.1.1
26.3.0