DECODE

Purpose

DECODE() compares the condition value to each search value one by one, and return the comparison result.

• If one or multiple search values are equal to the condition value, this function returns the result value following the first search value that is equal to the condition value.

• If no search value is equal to the condition value, this function returns the default value. If the default value is omitted, this function returns null.

Syntax

DECODE (condition, search, result [, search, result ...][, default])

Parameters

The condition, search, result, and default parameters can be set to a value or an expression of a numeric data type (NUMBER, FLOAT, BINARY_FLOAT, or BINARY_DOUBLE) or a character data type (CHAR, VARCHAR2, NCHAR, or NVARCHAR2).

Return type

• If the result value following the first search value that is equal to the condition value is of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 type, this function returns a value of the VARCHAR2 type.

• If the result value following the first search value that is equal to the condition value is of a numeric type, this function returns a result value of a numeric type.

• If no search value is equal to the condition value:

  • If the default value is omitted, this function returns null.

  • When the default value is of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 type, this function returns a value of the VARCHAR2 type.

Examples

• The following example uses the DECODE function to compare the values of 5 × 3 - 2 and 3 × 4 - 1.

  obclient> SELECT DECODE(SIGN((5*3-2)-(3*4-1)),0,'Equal',1,'(5 × 3 - 2) is greater','(3 × 4 - 1) is greater') FROM DUAL;
  +----------------------------------------------------------------------+
  | DECODE(SIGN((5*3-2)-(3*4-1)),0,'Equal',1,'(5 × 3 - 2) is greater','(3 × 4 - 1) is greater')     |
  +----------------------------------------------------------------------+
  | (5 × 3 - 2) is greater                                                            |
  +----------------------------------------------------------------------+
  1 row in set
  

• The following example uses the DECODE function to check whether the data contains the character S.

  obclient> SELECT
      DECODE(INSTR('CLARK','S'), 0, 'S not contained', 'S contained') "CLARK",
      DECODE(INSTR('KING','S'), 0, 'S not contained', 'S contained') "KING",
      DECODE(INSTR('MILLER','S'), 0, 'S not contained', 'S contained') "MILLER",
      DECODE(INSTR('ADAMS','S'), 0, 'S not contained', 'S contained') "ADAMS",
      DECODE(INSTR('FORD','S'), 0, 'S not contained', 'S contained') "FORD",
      DECODE(INSTR('JONES','S'), 0, 'S not contained', 'S contained') "JONES"
      FROM DUAL;
  +-------------+-------------+-------------+----------+-------------+----------+
  | CLARK       | KING        | MILLER      | ADAMS    | FORD        | JONES    |
  +-------------+-------------+-------------+----------+-------------+----------+
  | S not contained    | S not contained    | S not contained    | S contained   | S not contained    | S contained   |
  +-------------+-------------+-------------+----------+-------------+----------+
  1 row in set