How to check if a value is a number in SQLite

From the documentation,

The typeof(X) function returns a string that indicates the datatype of the expression X: "null", "integer", "real", "text", or "blob".

You can use where typeof(mycolumn) = "integer"

select * from mytable where abs(mycolumn) <> 0.0 or mycolumn = '0'

http://sqlfiddle.com/#!5/f1081/2

Based on this answer

SELECT * 
FROM mytable
WHERE columnNumeric  GLOB '*[0-9]*'

You could try something like this also:

select * from mytable where printf("%d", field1) = field1;

In case your column is text and contains numeric and string, this might be somewhat helpful in extracting integer data.

Example:

CREATE TABLE mytable (field1 text);
insert into mytable values (1);
insert into mytable values ('a');

select * from mytable where printf("%d", field1) = field1;
field1
----------
1