2 Ways to Return Non-Numeric Values in SQLite

The following SQLite examples return only those rows that have non-numeric values in a given column.

Sample Data

Let’s create a table with sample data:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
    c1
);

INSERT INTO t1 (c1) VALUES 
    (0),
    (1),
    (+1),
    (-1),
    (00.00),
    (73.45),
    (+73.45),
    (-73.45),
    (.246),
    (-.34e7),
    (12.e-3),
    (1.2e+4),
    ('Ten'),
    ('5 Dollars');

SELECT * FROM t1;

The table has now been created and contains the following data:

SQLite uses a dynamic type system, where the datatype of a value is associated with the value itself, not the column (as with most other RDBMSs). When I created the table, I didn’t specify a data type. Therefore, the values can be any type.

The `Typeof()` Function

We can use SQLite’s typeof() function to exclude numeric types (real and integer) from being returned in the query:

SELECT c1
FROM t1
WHERE typeof(c1) <> 'real'
AND typeof(c1) <> 'integer';

Result:

c1       
---------
Ten      
5 Dollars

Here’s another example that outputs the data type for each row:

SELECT 
    c1,
    typeof(c1)
FROM t1;

Result:

c1          typeof(c1)
----------  ----------
0           integer   
1           integer   
1           integer   
-1          integer   
0.0         real      
73.45       real      
73.45       real      
-73.45      real      
0.246       real      
-3400000.0  real      
0.012       real      
12000.0     real      
Ten         text      
5 Dollars   text

This shows us that only the last two rows contain non-numeric data (they contain text data).

The `REGEXP` Function

Another option is to use REGEXP to pass a regular expression to return just those values that match the given pattern.

Example:

SELECT c1
FROM t1 
WHERE c1 NOT REGEXP '^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$';

Result:

c1       
---------
Ten      
5 Dollars

Return Non-Integers

If we only want to return non-integers, we can do the following:

SELECT c1
FROM t1
WHERE typeof(c1) <> 'integer';

Result:

c1        
----------
0.0       
73.45     
73.45     
-73.45    
0.246     
-3400000.0
0.012     
12000.0   
Ten       
5 Dollars

This also excludes signed integers.

Or we could use a regular expression if our requirements are more specific:

SELECT c1
FROM t1 
WHERE c1 NOT REGEXP '^[0-9]+$';

Result:

c1        
----------
-1        
0.0       
73.45     
73.45     
-73.45    
0.246     
-3400000.0
0.012     
12000.0   
Ten       
5 Dollars

Does Not Contain Numeric Data

If we want to find rows that don’t contain any numeric data we can do the following:

SELECT c1 
FROM t1 
WHERE c1 NOT REGEXP '[0-9]+';

Result:

c1 
---
Ten

Sample Data

The Typeof() Function

The REGEXP Function

Return Non-Integers

Does Not Contain Numeric Data

The `Typeof()` Function

The `REGEXP` Function