DECODE

Purpose

The DECODE 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 corresponding to 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 corresponding to the first matching search value is a numeric data type, it returns a value of the corresponding numeric 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 character S exists 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