Search
Document Center

# DECODE

Last Updated: Jun 18, 2021

The `DECODE` function compares the `search` parameter with `condition` in sequence until the value of `condition` is equal to that of `search`. Then, the function returns the value of the `result` parameter that follows the corresponding `search`. If no `search` is equal to `condition`, the function returns the value of the `default` parameter.

## Syntax

``DECODE (condition, search 1, result 1, search 2, result 2 ... search n, result n, default)``

The meaning of the `DECODE` function can be interpreted by using the `IF...ELSE IF...END` statement.

``````IF condition = search 1 THEN
RETURN(result 1)
ELSE IF condition = search 2 THEN
RETURN(result 2)
......
ELSE IF condition = search n THEN
RETURN(result n)
ELSE
RETURN(default)
END IF``````

## Parameters

Parameter

Description

condition, search 1...search n, result 1...result n, default

The expression of the `NUMBER`, `FLOAT`, `BINARY_FLOAT`, or `BINARY_DOUBLE` numeric type or the `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` character type.

Notice

`search 1` to `search n` cannot be conditional expressions. In this case, you can execute only the `CASE WHEN THEN END` statement to resolve the issue.

``````WHEN CASE condition = search 1 THEN
RETURN(result 1)
ELSE CASE condition = search 2 THEN
RETURN(result 2)
......
ELSE CASE condition = search n THEN
RETURN(result n)
ELSE
RETURN(default)
END``````

## Examples

Example 1: Use `DECODE` to compare numeric values.

The following statement uses the `DECODE` function to return a smaller number between 10 and 20. The `SIGN()` function is used to calculate the sign of the difference between two values. The difference is a negative number and `SIGN()` returns -1 because 10 is less than 20. In this case, the `DECODE` function compares the -1 argument with the return value of the `SIGN()` function. If they are equal, 10 is returned. If they are not equal, 20 is returned.

``SELECT DECODE(SIGN(10-20),-1,10,20) FROM DUAL;``

The following result is returned:

``````+------------------------------+
| DECODE(SIGN(10-20),-1,10,20) |
+------------------------------+
|                           10 |
+------------------------------+``````

Example 2: Run the `DECODE` function to check whether the data includes the S character.

The following statements create the EMP table that contains the ename and sal columns and insert values into the table:

``````CREATE TABLE EMP(ename VARCHAR(30),sal NUMBER);
INSERT INTO EMP VALUES('CLARK', 2750);
INSERT INTO EMP VALUES('KING', 5300);
INSERT INTO EMP VALUES('MILLER', 1600);
INSERT INTO EMP VALUES('FORD', 3300);
INSERT INTO EMP VALUES('JONES', 3275);
INSERT INTO EMP VALUES('SCOTT', 3300);
INSERT INTO EMP VALUES('SMITH', 1100);
INSERT INTO EMP VALUES('ALLEN', 1900);
INSERT INTO EMP VALUES('BLAKE', 3150);
INSERT INTO EMP VALUES('JAMES', 1250);
INSERT INTO EMP VALUES('MARTIN', 1550);
INSERT INTO EMP VALUES('TURNER', 1800);
INSERT INTO EMP VALUES('WARD', 1550);``````

The following statement returns the occurrence position of the S character in the values of the ename column by using the `INSTR()` function. If the S character does not occur, 0 is returned. In this case, the `DECODE` function compares the return value of the `INSTR` function with 0. If they are equal, the S character does not occur in the values and the `DECODE` function returns S excluded. Otherwise, it returns S included.

``SELECT ENAME, SAL, DECODE(INSTR(ename, 'S'), 0, 'S excluded', 'S included') AS INFO FROM EMP;``

The following query result is returned:

``````+--------+------+------------+
| ENAME  | SAL  | INFO       |
+--------+------+------------+
| CLARK  | 2750 | S excluded    |
| KING   | 5300 | S excluded    |
| MILLER | 1600 | S excluded    |
| ADAMS  | 1400 | S included      |
| FORD   | 3300 | S excluded    |
| JONES  | 3275 | S included      |
| SCOTT  | 3300 | S included      |
| SMITH  | 1100 | S included      |
| ALLEN  | 1900 | S excluded    |
| BLAKE  | 3150 | S excluded    |
| JAMES  | 1250 | S included      |
| MARTIN | 1550 | S excluded    |
| TURNER | 1800 | S excluded    |
| WARD   | 1550 | S excluded    |
+--------+------+------------+``````