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".
