Figuring out if a text value in SQLite is a valid integer or float#

Given a table with a TEXT column in SQLite I want to figure out if every value in that table is actually the text representation of an integer or floating point value, so I can decide if it’s a good idea to change the type of the column (using sqlite-utils transform).

To do this efficiently, I want a SQLite idiom that will tell me if a string value is a valid integer or floating point number.

After much tinkering I’ve found two recipes for this that seem to work well.

This evaluates to true if value contains a valid integer representation:

1
cast(cast(value AS INTEGER) AS TEXT) = value

And this does the same thing for floating point numbers:

1
cast(cast(value AS REAL) AS TEXT) in (value, value || '.0')

The || '.0' bit there is needed because cast('1' as REAL) returns 1.0, not just 1.

(Note that 1.200 will not pass this test and will be incorrectly considered an invalid floating point representation)

1
select
2
  value,
3
  cast(cast(value AS REAL) AS TEXT) in (value, value || '.0') as is_valid_float
4
from
5
  (
6
    select
7
      '1' as value
8
    union
9
    select
10
      '1.1' as value
11
    union
12
    select
13
      'dog' as value
14
    union
15
    select
16
      null as value
17
  )

1
select
2
  value,
3
  cast(cast(value AS INTEGER) AS TEXT) = value as is_valid_int
4
from
5
  (
6
    select
7
      '1' as value
8
    union
9
    select
10
      '1.1' as value
11
    union
12
    select
13
      'dog' as value
14
    union
15
    select
16
      null as value
17
  )