database: portable way to compare two version number?

Question

What is a cross platform syntax to compare two version numbers? A version has two parts: major version and minor version, both are integer.

To compare two versions, first compare their major versions. If major versions are the same, compare their minor versions.

For example,

Product Table:

version  name
--------------
1.8      Bar
12.23    Foo
23.15    Hello

SQL

Select * from Product where version < "5.12"

It should return the first row with version 1.8. Is there any sql syntax which will works across different SQL platforms?

Specifically I'd like this to work on these platforms:

• oracle
• mysql
• SQL server
• sqlite

Answer 1

Core ANSI SQL-99 compliant:

select * from product
order by cast(version as int),
         cast(substring(version from position('.' in version) + 1) as int);

I.e. first sort by the integer part. Then sort by the "integer" following the . character.

Executes as:

SQL>create table product (version varchar(10), name varchar(10)); 
SQL>insert into product values ('1.8', 'Bar'); 
SQL>insert into product values ('12.23', 'Foo');
SQL>insert into product values ('23.15', 'Hello');
SQL>insert into product values ('1.11', 'Bye');
SQL>select * from product
SQL&order by cast(version as int),
SQL&         cast(substring(version from position('.' in version) + 1) as int); 
version    name
========== ==========
1.8        Bar
1.11       Bye
12.23      Foo
23.15      Hello

                  4 rows found

Note that some products have their own versions of ANSI SQL's substring() and position(). If you run into trouble try substr(version, value) etc.

EDIT: (cast(version as int) will fail in SQL Server (and Postgres) for strings that contain decimals – a_horse_with_no_name ) Make sure you only cast the integer part as integer:

select * from product
order by cast(substring(version from 1 for position('.' in version) -1) as int),
         cast(substring(version from position('.' in version) + 1) as int);