Semantic version sort with SQL

Question

How to sort semantic versions properly according to its rules in https://semver.org/ in SQL directly?

Examples: 1.0.0-alpha, 1.0.0-alpha.1, 1.0.0-0.3.7, 1.0.0-x.7.z.92, 1.0.0-x-y-z.

Answer 1

Once way is to have a following query:

SELECT * FROM table_of_versions ORDER BY
    INET_ATON(
        SUBSTRING_INDEX(
            CONCAT(
                SUBSTRING_INDEX(
                    SUBSTRING_INDEX(version, '+', 1), '-', 1),'.0.0.0')
                    ,'.',4)
            ),
    IF(
        LENGTH(version) = LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '+', 1), '-', 2), '-', -1)),
            "~", SUBSTRING_INDEX(
                    SUBSTRING_INDEX(
                        SUBSTRING_INDEX(version, '+', 1), '-', 2), '-', -1)
    ) DESC,
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(version, '+', 2), '+', -1);

But it doesn't work properly with build part.

More advance solutions to sort semantic versions in SQL properly, so the condition 1.0.0-alpha < 1.0.0 & 1.0.0-1 < 1.0.0 works perfectly, requires to add additional columns to your DB.

In case you need such solution, feel free to get know about it here: https://github.com/kotsky/semver-sort-py-sql