How to compare version strings in sqlite?

Question

I have a column that contains a version number as string like:

2.7.0.0
2.6.12.0
3.30.3.0
many more

How can I select all entries that have a version number lower than 2.7.0.0?

Since my last question got closed because the obviouse answere to my question is this:

WITH split(word, str) AS (
    SELECT '', '2.7.0.0'||'.'
    UNION ALL SELECT
    substr(str, 0, instr(str, '.')),
    substr(str, instr(str, '.')+1)
    FROM split WHERE str!=''
) SELECT word FROM split WHERE word!='';

I have multiple questions to that:

This Example just splits a string at .. How can I build that into a query and not just hardcode a string into it?. (Tryed to replace '2.7.0.0' with (select version from software) without luck)
Where do I need to put the version 2.7.0.0 to compare it to in this query?

Edit:

I got it to work with only the first number:

select version from software where (
    select word as major from (WITH split(word, str) AS (
        SELECT '', version||'.'
        UNION ALL SELECT
        substr(str, 0, instr(str, '.')),
        substr(str, instr(str, '.')+1)
        FROM split WHERE str!=''
    ) SELECT word FROM split WHERE word!='' LIMIT 1) where word = "2"
)

Clarify your question. Do you want to split the strings to get the sub-values, or compare them to each other for >, <, or == ?? — Mark Benningfield, Sep 30 '21 at 08:02
@MarkBenningfield It is writen in the title "[...] **compre** version string [...]" and also "how to select version numbers **lower** than [...]" I clearly mean to compare it. I did nowhere ask how to split the version string.. — somedude324334, Sep 30 '21 at 08:30
Okay then, are you saying that `select "colName" from "tableName" where "colName" < '2.7.0.0'` doesn't work? It should. — Mark Benningfield, Sep 30 '21 at 08:36
fml, that realy works... I didn't try it because I never would expect it to work... I did think way to complicated — somedude324334, Sep 30 '21 at 08:51

score 0 · Answer 1 · answered Sep 29 '21 at 17:26

If the number of parts is fixed, 4 parts always you can use a derived table (subquery) and instr substr functions. For example

SELECT productId, major
          , cast(substr(version, 0, instr(version, '.')) as decimal)  minor
          , cast(substr(version, instr(version, '.') + 1) as decimal(6,3)) build
FROM (
    SELECT productId, cast(substr(version, 0, instr(version, '.')) as decimal)  major, substr(version, instr(version, '.') + 1) version
    FROM (
       -- sample data
       select 1 productId, '2.7.0.0' version
       union all 
       select 2,  '2.6.12.0'
       union all 
       select 3,  '3.30.20.1' 
       union all 
       select 4,  '3.30.3.1' 
       ) myTable
    )t
 order by major, minor, build

Output

productId   major   minor   build
2   2   6   12
1   2   7   0
4   3   30  3.1
3   3   30  20.1

How to compare version strings in sqlite?

1 Answers1