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 columnNumeric  GLOB '*[0-9]*'

Answer 4

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

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

Based on this answer

Answer 5

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 6

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).

Answer 7

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 8

You can use the result of the function CAST( field as INTEGER) for numbers greater than zero and the simple condition like '0' per numbers equal to zero

SELECT *
FROM tableName
WHERE CAST(fieldName AS INTEGER) > 0
UNION
SELECT *
FROM tableName
WHERE fieldName like '0';

Answer 9

This answer is comprehensive and eliminates the shortcomings of all other answers. The only caveat is that it isn't sql standard... but neither is SQLite. If you manage to break this code please comment below, and I will patch it.

Figured this out accidentally. You can check for equality with the CAST value.

CASE   {TEXT_field}
    WHEN CAST({TEXT_field} AS INTEGER)                THEN 'Integer'     -- 'Number'
    WHEN CAST({TEXT_field} AS REAL)                   THEN 'Real'        -- 'Number'
    ELSE                                                   'Character'
END

OR

CASE   
    WHEN {TEXT_field} = CAST({TEXT_field} AS INTEGER) THEN 'Integer'     --'Number'
    WHEN {TEXT_field} = CAST({TEXT_field} AS Real)    THEN 'Real'        --'Number'
    ELSE                                                   'Character'
END

(It's the same thing just different syntax.)

• Note the order of execution. REAL must come after INTEGER.
• Perhaps their is some implicit casting of values prior to checking for equality so that the right-side is re-CAST to TEXT before comparison to left-side.

Updated for comment: @SimonWillison
I have added a check for 'Real' values
'1 frog' evaluated to 'Character' for me; which is correct
'0' evaluated to 'Integer' for me; which is correct

I am using SQLite version 3.31.1 with python sqlite3 version 2.6.0. The python element should not affect how a query executes.

Answer 10

Just adding this in case it helps people quickly test various permutations. For me: the round-trip cast int|text -> int -> text approach from DenverCR works in enough cases to be useful for my use-cases.

WITH
    def(input,expected) AS ( VALUES
        (0,             true),
        ('0',           true),
        ('00',          true),
        (1,             true),
        ('1',           true),
        (10,            true),
        ('10',          true),
        ('a',           false),
        (1.1,           false),
        ('1.1',         false),
        (-1,            true),
        ('-1',          true),
        ('1abc235xx',   false),
        ('1 frog',      false),
        ('7up',         false),
        ('up4it',       false),
        (NULL,          false)),
    test(input, expected, result) AS (
        SELECT 
            input, expected,
            CAST(CAST(input AS INTEGER) AS TEXT)
        FROM def)
SELECT input, result, result==input, expected FROM test

input	result	result==input	expected
0	0	1	1
0	0	1	1
00	0	0	1
1	1	1	1
1	1	1	1
10	10	1	1
10	10	1	1
a	0	0	0
1.1	1	0	0
1.1	1	0	0
-1	-1	1	1
-1	-1	1	1
1abc235xx	1	0	0
1 frog	1	0	0
7up	7	0	0
up4it	0	0	0
			0