DECODE

Purpose

This function compares the condition value to each search value one by one, and returns 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).

Notice

If the first result value is of a numeric data type, the returned result or default value must also be of a numeric data type. Otherwise, an error is reported.

Return data 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:

  • When 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

• Use 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
  

• Use 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