SQLite Forum

Efficiently checking if a TEXT column exclusively contains integers or floating point numbers (as strings)
You have to retrieve all the data and do the conversion/identification in your application and from that determine the applicable affinity to set in the new table, then after you create the table use INSERT INTO ... SELECT * FROM ... to actually convert the data.