SQL Dialects Reference/Functions and expressions/Misc expressions
Appearance
Misc functions
[edit | edit source]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) | ? |