SQLite 3 reference · 9 entries
SQLite Conditional and Type Functions
Handle NULLs and conditions in SQLite with coalesce(), ifnull(), nullif(), iif() and CASE, and check or convert types with typeof() and CAST.
SQLite columns are flexibly typed: a value's storage class (typeof()) can differ from the declared
column type. NULL is not equal to anything, including NULL, so use IS NULL, coalesce() or
ifnull() to handle it.
| Name | Description | Since |
|---|---|---|
COALESCE() |
Return the first argument that isn't NULL. The standard way to replace NULL with a default value. | |
IFNULL() |
Return the first argument if it isn't NULL, otherwise the second. A two-argument coalesce(). | |
NULLIF() |
Return NULL if two values are equal, otherwise the first value. Often used to avoid division by zero. | |
IIF() |
Return one value if a condition is true and another if it's false: a short form of CASE WHEN … THEN … ELSE … END. | 3.32.0 |
CASE |
Choose a value from conditions: CASE WHEN cond THEN result … ELSE default END. Works in SELECT, WHERE and ORDER BY. | |
TYPEOF() |
Return the storage class of a value: 'null', 'integer', 'real', 'text' or 'blob'. Shows how SQLite stored it. | |
CAST |
Convert a value to another type: CAST(X AS INTEGER), REAL, TEXT, NUMERIC or BLOB. Text that isn't a number becomes 0. | |
LAST_INSERT_ROWID() |
Return the rowid of the most recent successful INSERT on this connection, such as a new auto-generated ID. | |
CHANGES() |
Return how many rows the most recent INSERT, UPDATE or DELETE changed. total_changes() counts since the connection opened. |