SQL Dialects Reference/Functions and expressions/Misc expressions

From Wikibooks, open books for an open world
Jump to navigation Jump to search

Misc functions[edit | edit source]

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version Feature Standard
SQL:2011
DB2 Firebird Ingres Linter MSSQL MySQL
Vers. 5.x
MonetDB Oracle
Vers. 11.x
PostgreSQL SQLite Virtuoso
? Convert value val to data type type CAST(val AS type) ? CAST(val AS type) ? CAST(val AS type)
CASTexpression as type
TO_CHAR
TO_NUMBER
TO_DATE
CAST(val AS type)
CONVERT(type, val, style)
CONVERT(val, type)
CAST(val AS type)
CAST(val AS type)
CONVERT(val, type)
CAST(val AS type) CAST(val AS type)
val::type
CAST(val AS type) ?
? Replace NULL within a val with a fallback value, return val intact, if it's non-NULL COALESCE(val, fallback) ? ? ? NVL(val, fallback) COALESCE(val, fallback)
ISNULL(val, fallback)
IFNULL(val, fallback) COALESCE(val, fallback) COALESCE(val, fallback)
NVL(val, fallback)
COALESCE(val, fallback) IFNULL(val, fallback)
COALESCE(val, fallback)
?
? Return the first non-NULL value from a list of values (val1, val2, ...) COALESCE(val1, val2, ...) COALESCE(val1, val2) COALESCE(val1, val2, ...) ? COALESCE(val1, val2, ...) COALESCE(val1, val2, ...) COALESCE(val1, val2, ...) COALESCE(val1, val2, ...) COALESCE(val1, val2, ...) COALESCE(val1, val2, ...) COALESCE(val1, val2, ...) ?
? Convert particular values to NULL: return NULL if a = b otherwise keep a NULLIF(a, b) ? NULLIF(a, b) ? NULLIF(a, b) NULLIF(a, b) NULLIF(a, b) NULLIF(a, b) NULLIF(a, b) NULLIF(a, b) NULLIF(a, b) ?