<<<<<<< HEAD decode | H2 | SQLDoc
Skip to main content

decode

1. DECODE Function​

======= decode | H2 Function | SQLDoc

decode

1. DECODE Function​

>>>>>>> 9d19b1c8cb744c8e86d5c15b74d8b5f719ad62be

The DECODE function in H2 database is used to perform conditional logic and return different values based on specified conditions.

2. Syntax​

The syntax for the DECODE function in H2 database is as follows:

DECODE(expression, search_value1, result1, search_value2, result2, ..., default_result)

Arguments​

  • expression: The value to be compared against the search values.
  • search_value1: The value to search for in the expression.
  • result1: The value to return if search_value1 matches the expression.
  • search_value2: The value to search for in the expression.
  • result2: The value to return if search_value2 matches the expression.
  • default_result: The value to return if none of the search values match the expression.

Return​

  • The DECODE function returns the result corresponding to the first matching search value. If no match is found, the default result is returned.

3. Notes​

  • The DECODE function in H2 database is similar to the CASE statement and can be used as an alternative for conditional logic.
  • The number of search value and result pairs can be variable, allowing for multiple conditions to be checked.
  • If the expression matches multiple search values, the result corresponding to the first match is returned.
  • If no match is found and no default result is specified, the DECODE function returns NULL.
  • The DECODE function in H2 database is case-sensitive, so ensure that the search values and expressions match exactly.

4. Examples​

Here are a few examples demonstrating the usage of the DECODE function in H2 database:

Example 1 - Single condition:​

SELECT DECODE(2, 1, 'One', 2, 'Two', 'Other') AS result;

Output:

result
------
Two

Example 2 - Multiple conditions:​

SELECT DECODE('Apple', 'Orange', 'Fruit is Orange', 'Apple', 'Fruit is Apple', 'Other Fruit') AS result;

Output:

result
--------------
Fruit is Apple

Example 3 - Using a default result:​

SELECT DECODE(5, 1, 'One', 2, 'Two', 'No Match Found') AS result;

Output:

result
----------------
No Match Found
  • CASE - Perform conditional logic with multiple conditions and results.
  • IFNULL - Return a specified value if the expression is NULL.
  • coalesce - Return the first non-NULL expression from a list.