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.
SELECT TYPEOF(price), COUNT(*) FROM products GROUP BY TYPEOF(price);
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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. |
-- PostgreSQL SELECT pg_typeof(1 + 1.5); -- numeric
Usage notes
- Column types are only affinities. A column declared
INTEGERstill accepts'n/a'unless aCHECK (TYPEOF(col) = 'integer')or aSTRICTtable stops it. See type affinity. - Integer division.
TYPEOF(7 / 2)isinteger(and the value is3). Use7 / 2.0for a real result. - Text that looks like a number is converted when it meets arithmetic (
'7' + 1is8), 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.