DECODE

​

Syntax

​

Conditional (scalar)

decode(value, search1, result1 [, search_n, result_n ...] [, default_value])

• All result alternatives must have compatible types. Matching types are preserved; mixed types may be converted to a common type. Use explicit casts to choose the result type.

​

Charset (scalar)

decode(binary_value, charset)

​

Arguments

​

Conditional

​

Charset

​

Returns

• Type: Common SQL type after conversion of the conditional result/default values
• NULL behavior: NULL matches a NULL search value. No match and no default returns NULL.

​

Charset

• Type: STRING
• Possible values: Decoded text.
• NULL behavior: NULL bytes return NULL when charset is a valid non-NULL literal. NULL or nonconstant charset raises an error.

​

Properties

​

Usage notes

• The first matching search/result pair wins. Matching uses NULL-safe equality.
• Two arguments select the binary/charset form; three or more arguments select conditional matching.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
• Query tools may report a DECIMAL result as DOUBLE or a TIME result as INTEGER. The returned value still keeps its decimal precision and scale, or its time type. A reported type does not convert the value.
• Deterministic: The same inputs and settings produce the same result.

​

Examples

​

Typical behavior

SELECT decode(CAST(NULL AS INT), CAST(NULL AS INT), 'match', 'other') AS "value";

​

Decode binary bytes

SELECT decode(X'4869', 'UTF-8') AS "value";

​

Limitations

• UTF-8 and ASCII invalid bytes are replaced with U+FFFD. UTF-16 rejects odd byte counts or invalid surrogate sequences. UTF-16 detects a BOM and otherwise defaults to big endian.
• A charset is a call argument and must be a non-NULL constant. It is not a session parameter.
• Conditional search operands and result branches must have compatible types.

​

Related functions

• if
• nvl
• encode