DECODE

Purpose

This function compares condition with each search value in sequence and returns the corresponding result.

• If one or more search values are equal to condition, it returns the value of the first matching result.

• If no search values are equal to condition, it returns the value of the default parameter. If the default parameter is omitted, it returns null.

Syntax

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

Parameters

condition, search, result, and default can be values or expressions of numeric data types (NUMBER, FLOAT, BINARY_FLOAT, or BINARY_DOUBLE) or character data types (CHAR, VARCHAR2, NCHAR, or NVARCHAR2).

Return type

• If the data type of the result value that follows the first matching search value is CHAR, VARCHAR2, NCHAR, or NVARCHAR2, it returns a value of the VARCHAR2 data type.

• If the data type of the result value that follows the first matching search value is a numeric data type, it returns a value of that data type.

• If none of the search values are equal to condition:

  • If the default parameter is omitted, it returns null.

  • If the data type of the default parameter is CHAR, VARCHAR2, NCHAR, or NVARCHAR2, it returns a value of the VARCHAR2 data type.

Examples

• Use DECODE 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 larger','(3*4-1) is larger') FROM DUAL;
  +----------------------------------------------------------------------+
  | DECODE(SIGN((5*3-2)-(3*4-1)),0,'equal',1,'(5*3-2) is larger','(3*4-1) is larger')     |
  +----------------------------------------------------------------------+
  | (5*3-2) is larger                                                            |
  +----------------------------------------------------------------------+
  1 row in set
  

• Use the DECODE function to check whether the data contains the character S.

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