Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Scalar Functions

SQL TYPEOF() function

Reference for TYPEOF() in SQLite: see whether a value is integer, real, text, blob or null, how type affinity changes it, and the equivalents in other databases.

TYPEOF(x) returns the storage type of a single value as one of five lowercase words: 'null', 'integer', 'real', 'text' or 'blob'. It is specific to SQLite, which is dynamically typed: the type belongs to each stored value, not only to the column.

That makes TYPEOF() the first thing to reach for when a query behaves strangely, for example when a number was stored as text and sorts or compares unexpectedly.

SQL
SELECT TYPEOF(price), COUNT(*)
FROM products
GROUP BY TYPEOF(price);

Try it

SQL The five storage classes

Press Run (or Ctrl + Enter) to see the result here.

SQL Finding values of the wrong type ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
TYPEOF(x)
Name Description
x Any expression.

Returns: one of 'null', 'integer', 'real', 'text', 'blob'. It never returns NULL: for a NULL argument the answer is the text 'null'.

Dialect differences

Database Equivalent
SQLite TYPEOF(x) (value type)
PostgreSQL pg_typeof(x) returns the data type name, such as integer or text. Types are fixed by the column, so it describes the expression, not a per-row value.
MySQL No function for expressions; check the declared type with DESCRIBE table or information_schema.columns.
SQL Server SQL_VARIANT_PROPERTY(x, 'BaseType') for sql_variant values; column types from sys.columns.
Oracle DUMP(x) shows the internal type code and bytes.
SQL
-- PostgreSQL
SELECT pg_typeof(1 + 1.5);    -- numeric

Usage notes

  • Column types are only affinities. A column declared INTEGER still accepts 'n/a' unless a CHECK (TYPEOF(col) = 'integer') or a STRICT table stops it. See type affinity.
  • Integer division. TYPEOF(7 / 2) is integer (and the value is 3). Use 7 / 2.0 for a real result.
  • Text that looks like a number is converted when it meets arithmetic ('7' + 1 is 8), but stays text in comparisons with other text.
  • Use it for data checks: WHERE TYPEOF(amount) <> 'real' finds rows that do not hold what you expect.
  • Sorting across types: SQLite orders NULL, then numbers, then text, then blobs, which is why mixed columns sort in surprising groups.
  • Not portable. Prefer constraints and proper column types in databases that enforce them.