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 the condition value, it returns the value of the result that follows the first matching search value.

• If no search value is equal to the condition value, 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 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 that follows the first matching search value is a numeric data type, it returns a value of the same data type.

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

  • 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 character S is present in the data.

  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