SQLite Forum

Efficiently checking if a TEXT column exclusively contains integers or floating point numbers (as strings)
I often import data from CSV files (usually from government open data portal websites).

This results in a table where every column is of type TEXT - but I'd like to convert columns that appear to exclusively contain integers or floating models to INTEGER or REAL column.

So I'm writing code to detect the suggested type for a column, and I want to do it as efficiently as possible/

I've worked out the following recipe for detecting a text column which is full of integers:

  'contains_non_integer' as result
  cast(cast(mycolumn AS INTEGER) AS TEXT) != mycolumn

This query will scan through the table in (I think) primary key order and will terminate and return a single row with a single result in it the moment it runs across the first row that contains a text value that doesn't represent an integer.

If the column does contain exclusively integers, it will scan the entire column and eventually return zero rows at the end.

So two questions:

1. Is this the most efficient way to do this? Is there another query I can use that will be more effective?
2. What's an equivalent recipe for floating point numbers? I've not yet figured one out.