SQLite Forum

Yup, I understand column affinities.

The problem I'm trying to solve is, given a CSV file produced by someone else entirely, how can I efficiently detect "every single value in the category column consists of a string of integer characters" - so I can automatically create a new table with the correct affinities and then execute that conversion via in insert (using the transform tool I wrote about here: <https://simonwillison.net/2020/Sep/23/sqlite-advanced-alter-table/> )

So for example say I have a table called `people` that looks like this, where every column is of TEXT affinity:

| name   | category | score |
|--------|----------|-------|
| Joan   | 1 | 3.5 |
| Rahul   | 2 | 4.5 |
| Amanda   | 1 | 1.1 |

I plan to run SQL queries that detect that the `category` column contains only strings that are valid integers, and the `score` column contains only strings that are floating point numbers - so I can then automatically create a new table with the correct affinities and copy the data across.

This query is the best I've come up with for efficiently answering the question "does the category column contain any strings that do NOT work as integers" - but I'm not sure if it's the best approach, and I'm also interested in figuring out an equivalent mechanism that deals with floating point numbers.

```
select
  'contains_non_integer' as result
from
  people
where
  cast(cast(category AS INTEGER) AS TEXT) != category
limit
  1
```