1

I have a VARCHAR name field in my database that contains both characters and decimals to represent version numbers of software. Given the example data below, how can I order the results in the correct order?

name
--------------------
Version 1.7
Version 1.8
Version 1.9
Version 1.10
Version 2.2
Version 3.0
Version 3.3

The problem, by default, is that ordering the results by name only, results in something like this:

name
--------------------
Version 1.10
Version 1.7
Version 1.8
Version 1.9
Version 2.2
Version 3.0
Version 3.3

This has been asked before on StackOverflow and I've been through and tested many of the answers provided but without luck. It's also difficult to find a solution that works for a similar result set, namely a mix of alphanumerical and decimal.

I have tried:

ORDER BY CAST(name AS INTEGER)
ORDER BY CAST(name AS DECIMAL)
ORDER BY CAST(name AS DECIMAL(4,2)
- same as above but CONVERT(name, [type])
ORDER BY ABS(name)
- some other things which have since slipped my mind :/

Things to note:

  • The name field is not always prefixed with 'Version'. Sometimes it's 'Deprecated' or a number of other things.
  • The name field does not always contain decimal version numbers. It can sometimes be a word on its own.
  • The name field could also contain items like Plugin Name 2 and Go2URL.
  • The problem item is 'Version 1.10'. All of the methods I have tried so far have led to this item being out of place.

Workaround:

I've worked around this issue temporarily by adding a new VARCHAR field called canonicalVersion and storing a canonical version number. So, 1.10.210 becomes 0001.0010.0210.0000 and is therefore simply sortable with ORDER BY canonicalVersion ASC and all items are now in the correct order.

Although I've worked around the issue for now, I would still like to see a solution to the original question without adding an extra field.

Martin James
  • 902
  • 1
  • 9
  • 25
  • 1
    The stated duplicate is not a duplicate of this question. This has variable length prefixes and there are not multiple levels for the versions. – Gordon Linoff Jun 25 '18 at 16:38

1 Answers1

0

I think you can use substring_index() and some manipulation:

order by left(name, length(name) - length(substring_index(name, ' ', -1)) - 1),
         substring_index(name, ' ', -1) + 0

This assumes that the version numbers themselves can be converted to numbers.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for taking the time to look at and answer my question. I have just tried your solution which results in the following error: '... check the manual that corresponds to your MySQL server version for the right syntax to use near ') + 0' at line 2'. I removed the trailing `)` from the end of last `order by` clause which got it working but the order of the results are still incorrect, with `1.10` coming out first. – Martin James Jun 25 '18 at 16:48