SQL Dialects Reference/Functions and expressions/Misc expressions

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

Misc functions[edit]

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(val, type)
CONVERT(val, type)
CAST(val AS type)
CAST(val AS type) CAST(val AS type) CAST(val AS type)
val::type
 ?  ?
 ? Replace NULL within a val with a fallback value, return val intact, if it's non-NULL COALESCE(val, fallback)  ?  ?  ? NVL(val, fallback) ISNULL(val, fallback) IFNULL(val, fallback) IFNULL(val, fallback) NVL(val, fallback) N/A N/A  ?
 ? 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, ...)  ?
 ? Return NULL if two values a and b are equal NULLIF(a, b)  ? NULLIF(a, b)  ? NULLIF(a, b) NULLIF(a, b) NULLIF(a, b)  ? NULLIF(a, b) NULLIF(a, b)  ?  ?