CODEWITHSUNDEEP
sqlsqlite
Chin
Chin

Reputation: 20675

How to check if a value is a number in SQLite

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)

Upvotes: 16

Views: 29735

Answers (10)

monojohnny
monojohnny

Reputation: 6181

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

Upvotes: 0

DonkeyKong
DonkeyKong

Reputation: 1091

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.

Upvotes: 0

Raymond Nijland
Raymond Nijland

Reputation: 11602

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

Upvotes: 2

DenverCR
DenverCR

Reputation: 1491

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

Upvotes: 3

Antonio Valanzano
Antonio Valanzano

Reputation: 1

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';

Upvotes: 0

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112342

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.

Upvotes: 9

Richard Kelly
Richard Kelly

Reputation: 195

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

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

Based on this answer

Upvotes: 8

Meysam Chegini
Meysam Chegini

Reputation: 999

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

Upvotes: 9

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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"

Upvotes: 23

zedfoxus
zedfoxus

Reputation: 37059

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

Upvotes: 16

Related Questions