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:

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

And this does the same thing for floating point numbers:

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)

Demos

The float version:

select
  value,
  cast(cast(value AS REAL) AS TEXT) in (value, value || '.0') as is_valid_float
from
  (
    select
      '1' as value
    union
    select
      '1.1' as value
    union
    select
      'dog' as value
    union
    select
      null as value
  )

Try that here

value	is_valid_float
null	null
1	1
1.1	1
dog	0

The integer version:

select
  value,
  cast(cast(value AS INTEGER) AS TEXT) = value as is_valid_int
from
  (
    select
      '1' as value
    union
    select
      '1.1' as value
    union
    select
      'dog' as value
    union
    select
      null as value
  )

Try that here

value	is_valid_int
null	null
1	1
1.1	0
dog	0

Created 2020-09-27T13:40:22-07:00, updated 2020-12-29T13:55:23-08:00 · History · Edit

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

Demos

Related