How Typeof() Works in SQLite

Posted on by Ian

The SQLite typeof() function allows you to determine the data type of an expression.

An expression can be one of the following data types:

  • null
  • integer
  • real
  • text
  • blob

Example

Here’s an example to demonstrate.

SELECT typeof('Avocado');

Result:

text

Here are some more:

SELECT 
  typeof(12),
  typeof(X'12'),
  typeof(1.23),
  typeof(NULL);

Result:

typeof(12)  typeof(X'12')  typeof(1.23)  typeof(NULL)
----------  -------------  ------------  ------------
integer     blob           real          null        

Database Example

SQLite uses a dynamic type system, as opposed to static typing like what is used in most other relational database management systems.

In SQLite, the datatype of a value is associated with the value itself, not with its container. This means a column can contain data of various types. For example, one row might have text in that column, another row might contain an integer.

Here’s an example that demonstrates this concept.

Take the following table called Products:

ProductId   ProductName     Price     
----------  --------------  ----------
1           Homer's Widget  10.0      
2           Peter's Widget  20.75     
3           Bob's Widget    15.0      
4           777                       

In this table, the data in the last row appears to be different to the others.

Let’s see what the typeof() function returns.

SELECT 
  ProductId,
  typeof(ProductName),
  typeof(Price)
FROM Products;

Result:

ProductId   typeof(ProductName)  typeof(Price)
----------  -------------------  -------------
1           text                 real         
2           text                 real         
3           text                 real         
4           integer              null         

As expected, the last row returns different data types for the ProductName and Price columns.

Expressions

The typeof() function can be handy to see what data type a given expression might return.

SELECT 
 typeof(1),
 typeof(1 + 1.5),
 typeof(1 + NULL);

Result:

typeof(1)   typeof(1 + 1.5)  typeof(1 + NULL)
----------  ---------------  ----------------
integer     real             null