DECODE

Purpose

This function compares condition with each search in sequence and returns the result of the comparison.

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

• If none of the 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 result that follows the first search equal to condition is of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type, it returns a value of the VARCHAR2 data type.

• If the result that follows the first search equal to condition is of a numeric data type, it returns a value of the same data type as the result parameter.

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

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

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

Examples

• Use DECODE to compare the sizes of 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