All Products
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('ADAMS', 1400);       
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    |
+--------+------+------------+