Sqlite: Iterating through substrings of a string field

Question

I have a field which contains a string of numbers

 "2002 2005 2001 2006 2008 2344"

It is preferred that we assume the spaces are the splits, else failing that, that they are 4 characters long each.

I can select a substring:

SELECT substr(years,1,4)  FROM TABLE 

But have no idea how to check each one. I am trying to find the row that contains the number closest to 0.

and I can ORDER BY and LIMIT 1.

Answer 1

This looks UGLY but it selects all you need in one SQLlite statement. Also you can do some optimization if assume dates are in the current interval say 1900..2100. In this case you can cut first 2 selects B,C (B: 1 union 2) (C: 9 union 0 union 1)

 select years2,years from 
    (
    select i1||i2||i3||i4  as years2 from (select '0' as i1 
                   union all 
                   select '1' as i1
                   union all 
                   select '2' as i1
                   union all 
                   select '3' as i1
                   union all 
                   select '4' as i1 
                   union all 
                   select '5' as i1 
                   union all 
                   select '6' as i1
                   union all 
                   select '7' as i1
                   union all 
                   select '8' as i1
                   union all 
                   select '9' as i1) B, 
    (select '0' as i2 
                   union all 
                   select '1' as i2
                   union all 
                   select '2' as i2
                   union all 
                   select '3' as i2
                   union all 
                   select '4' as i2 
                   union all 
                   select '5' as i2 
                   union all 
                   select '6' as i2
                   union all 
                   select '7' as i2
                   union all 
                   select '8' as i2
                   union all 
                   select '9' as i2) C,
    (select '0' as i3 
                   union all 
                   select '1' as i3
                   union all 
                   select '2' as i3
                   union all 
                   select '3' as i3
                   union all 
                   select '4' as i3 
                   union all 
                   select '5' as i3 
                   union all 
                   select '6' as i3
                   union all 
                   select '7' as i3
                   union all 
                   select '8' as i3
                   union all 
                   select '9' as i3) D,
                  (select '0' as i4 
                   union all 
                   select '1' as i4
                   union all 
                   select '2' as i4
                   union all 
                   select '3' as i4 
                   union all 
                   select '4' as i4 
                   union all 
                   select '5' as i4 
                   union all 
                   select '6' as i4
                   union all 
                   select '7' as i4
                   union all 
                   select '8' as i4
                   union all 
                   select '9' as i4) E
    ) YearsAll

   left join YearsTable on (YearsTable.years like '%'||YearsAll.years2||'%')

    where YearsTable.years is not null

    order by years2 limit 1

Answer 2

You can use a common table expression to split out the year in the years string and then get the year closest to 0.

WITH split_years(year, years) AS (
    values(0, '2001 2002 2003 2004 2005 2006'||' ')
    UNION ALL SELECT
    cast(substr(years, 0, instr(years, ' ')) as int),
    substr(years, instr(years, ' ')+1)
    FROM split WHERE length(years)
) SELECT year FROM split_years WHERE year>0 ORDER BY year LIMIT 1;

Output is:

2001