DECODE Function

DECODE


DECODE(expr, search1, result1 [, search2, result2,] ... [, default])

Description

The DECODE function is a conditional expression function that returns the corresponding resultN expression based on the matching result of the given expr expression with the searchN conditional expressions. When expr is equal to any searchN, the corresponding resultN is returned. If expr is not equal to any searchN and a default result expression is provided, default is returned. If no default is provided, null is returned.

When making comparisons, null and null are considered equal.

Parameter Description

expr: The expression to be matched, of any type.
searchN: Conditional expressions of the same type as expr, used for comparison with expr.
resultN: Result expressions, which must be of the same type as searchN.
default (optional): Default result expression, returned when expr is not equal to any searchN, must be of the same type as resultN.

Example

Basic usage:


SELECT DECODE(3, 6, 'Lakehouse', NULL, 'SQL', 4, 'compiler');
-- Result: 'compiler'

In this example, expr is 3, which is not equal to search1 (6), but is equal to search2 (4), so it returns result2 ('compiler').

Using default values:


SELECT DECODE(5, 6, 'Lakehouse', NULL, 'SQL', 4, 'compiler', 'default');
-- Result: 'default'

When expr is 5, it does not match any of the searchN values, so the specified default value ('default') is returned.

Handling null values:


SELECT DECODE(NULL, NULL, 'Lakehouse', 6, 'SQL', NULL, 'default');
-- Result: 'Lakehouse'

In this example, expr is null, which is equal to search1 (null), so it returns result1 ('Lakehouse').

Use in combination with other functions:


SELECT DECODE(SUBSTR('Hello World', 1, 1), 'H', 'Yes', 'W', 'No', 'default');
-- Result: 'Yes'

In this example, we use the SUBSTR function to get the first character 'H' of the string 'Hello World', and then use the DECODE function to determine whether the character is 'H'. Since expr is equal to search1, it returns result1 ('Yes').

Through the above example, you can better understand the usage and functionality of the DECODE function. Please adjust the parameters and expressions according to your actual needs.