How to check if a value is a number in SQLite

Question

I have a column that contains numbers and other string values (like "?", "???", etc.)

Is it possible to add an "is number" condition to the where clause in SQLite? Something like:

select * from mytable where isnumber(mycolumn)

Answer 1

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"

Answer 2

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

Answer 3

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

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

Based on this answer

Answer 4

To test whether the column contains exclusively an integer with no other alphanumeric characters, use:

NOT myColumn GLOB '*[^0-9]*' AND myColumn LIKE '_%'

I.e., we test whether the column contains anything else than a digit and invert the result. Additionally we test whether it contains at least one character.

Note that GLOB '*[0-9]*' will find digits nested between other characters as well. The function typeof() will return 'text' for a column typed as TEXT, even if the text represents a number. As @rayzinnz mentioned, the abs() function is not reliable as well.

Answer 5

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

Answer 6

As SQLite and MySQL follow the same syntax and loose datatypes.

The query below is also possible

SELECT 
   <data>
 , (
     LENGTH(CAST(<data> AS UNSIGNED))
   )
     =
  CASE WHEN CAST(<data> AS UNSIGNED) = 0
  THEN CAST(<data> AS UNSIGNED)
  ELSE (LENGTH(<data>)
  ) END AS is_int;

Note the <data> is BNF you would have the replace those values.

This answer is based on mine other answer

Running SQLite demo

Answer 7

For integer strings, test whether the roundtrip CAST matches the original string:

SELECT * FROM mytable WHERE cast(cast(mycolumn AS INTEGER) AS TEXT) = mycolumn

For consistently-formatted real strings (for example, currency):

SELECT * FROM mytable WHERE printf("%.2f", cast(mycolumn AS REAL)) = mycolumn

Input values:

• Can't have leading zeroes
• Must format negatives as -number rather than (number).