CASE_WHEN_VALUE()

All functions > CONDITIONAL > CASE_WHEN_VALUE()

Returns the result corresponding to the first matching value in a simple case expression

Signatures

Returns: the value corresponding to the first match, or else value if none match

CASE_WHEN_VALUE(when_base: T, values_to_match: ARRAY<T>, then: ARRAY<U>, else: U) → U

Notes

• The same logic is usually written as CASE <expr> WHEN … THEN … [ELSE …] END (see the CASE WHEN VALUE operator page). This function is the registry encoding.
• Compares when_base against each value in values_to_match using equality
• values_to_match and then arrays must have the same length
• All then values and else value must be of compatible types

Examples

SELECT
    f1 := CASE_WHEN_VALUE(2, ARRAY[1, 2, 3], ARRAY['a', 'b', 'c'], 'z'), -- Value 2 matches position 2, returns 'b'
    f2 := CASE_WHEN_VALUE(5, ARRAY[1, 2, 3], ARRAY['a', 'b', 'c'], 'z'), -- Value 5 not in array, returns else value
    f3 := CASE_WHEN_VALUE('cat', ARRAY['dog', 'cat', 'bird'], ARRAY[1, 2, 3], 999) -- String matching
;