CODEWITHSUNDEEP
sqlsqlite
Robin Rodricks
Robin Rodricks

Reputation: 113976

Check if contents of TEXT column are purely numeric

I've got an Sqlite DB where the data looks purely numeric (integers) and the column is typed TEXT. I would like to type it as INTEGER if possible.

What query can check if every cell of a certain column can be successfully casted to INT?

SELECT * FROM table WHERE INT(column) != NULL

Alternatively I would like to check if the cells are numeric (don't have any letters/symbols)

SELECT * FROM table WHERE column NOT LIKE "%a-z%"

As a side note, I wanted to do this to reduce the size of the DB, but since Sqlite uses dynamic typing (per cell typing) would this have ANY effect on the size of the DB?

Upvotes: 3

Views: 1942

Answers (1)

CL.
CL.

Reputation: 180060

You have to check whether all values can be converted into an integer:

SELECT *
FROM MyTable
WHERE CAST(MyColumn AS INTEGER) IS NOT MyColumn;

Upvotes: 5

Related Questions