MySQL — comparing dotted version strings

Contents

  1. 1. Filter by version range
  2. 2. Sort by version only

Use case: pick the newest row whose version is still less than or equal to the current app version.

String compare bites you:

1
select "5.1.3" > "5.1.24";

MySQL returns 1 (true) because it compares lexicographically — at the patch segment, 3 is greater than 2.

Stack Overflow has a solid thread on this:
compare version numbers in MySQL

Assume x.y.z (fixed number of segments).

Filter by version range

Pad each segment to N digits (e.g. 5), concatenate, then compare the packed string:

1
2
3
4
5
6
7
8
9
-- latest row with version <= 12.0.2
SELECT *, CONCAT(
LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 1), '.', -1), 5, '0'),
LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 2), '.', -1), 5, '0'),
LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 3), '.', -1), 5, '0')
) vcode
FROM foo
HAVING vcode <= CONCAT(LPAD(12, 5, '0'), LPAD(0, 5, '0'), LPAD(2, 5, '0'))
ORDER BY vcode DESC;

sqlfiddle example


Sort by version only

When every segment is numeric, order by each part as a number:

1
2
3
4
5
6
SELECT Version
FROM foo
ORDER BY
SUBSTRING_INDEX(version, '.', 1) * 1,
SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 2), '.', -1) * 1,
SUBSTRING_INDEX(version, '.', -1) * 1;

sqlfiddle example

Questions? Leave a comment below or email: newbvirgil@gmail.com