CODEWITHSUNDEEP
phpmysql
tomysshadow
tomysshadow

Reputation: 932

MySQL Order By Version Number + Name

I have a table of version numbers for a program in a MySQL database, organized like this:

versionnumber
----
1.4.0
1.5.0
1.6
1.6.1
1.6.2
1.7DevBuild1
1.7DevBuild2
1.7DevBuild3
1.7
1.8

I looked up how to order by version numbers, with dots, in MySQL queries, and found a pretty creative solution elsewhere on SO.

SELECT * 
FROM versions
ORDER BY INET_ATON(SUBSTRING_INDEX(CONCAT(versionnumber,'.0.0.0'),'.',4))

This works by ordering the version number as if it were an IP Address in INET_ATON. It works. Sort of. It organizes the version numbers correctly, but it doesn't recognize the Dev builds, instead putting them to the top and ignoring them because they contain text, which IP Addresses should not contain.

1.7DevBuild1
1.7DevBuild2
1.7DevBuild3
1.4.0
1.5.0
1.6
1.6.1
1.6.2
1.7
1.8

so I had an idea: replace all the letters a-z with nothing, and that way it will contain only numbers and dots. But MySQL can't do RegExp in replace. Is there another way or I have to sort the returned array in PHP?

Now, someone might suggest to just change "DevBuild3" to ".3", but then users looking for that old version will see a ".3" instead of "DevBuild3".

Upvotes: 1

Views: 1457

Answers (1)

Barmar
Barmar

Reputation: 780994

If you can split versionnumber into two columns, versionnumber and versionsuffix, you can use:

ORDER BY INET_ATON(SUBSTRING_INDEX(CONCAT(versionnumber,'.0.0.0'),'.',4)), versionsuffix

Upvotes: 4

Related Questions