Decode function is more like an IF-ELSE-ELSIF-ENDIF kind of condition. The Decode function format has an expression and a search value to search for. If the expression matches with the search value, then a corresponding result is given back by the function.

The format of DECODE function is:

DECODE (expression, search value_1, result1,
search value_2, result2,
search value_3, result3,
….);

It means – the code will check if the result of the expression matches with the search value_1, then gives back result1 as the output, if the result of the expression matches with search value_2, then gives back result2 as the output and so on and so forth.

Example: In order to understand DECODE function, let’s first create the required data. Use the following statements to create table and insert data.

CREATE TABLE xx_decode_test
(
    emp_name     VARCHAR2 (200),
    emp_id       NUMBER,
    dept_id      NUMBER,
    join_date    DATE
);

SELECT * FROM XX_DECODE_TEST;

INSERT INTO xx_decode_test
     VALUES ('Adam Smith',
             101989,
             10,
             '10-JUN-10');

INSERT INTO xx_decode_test
     VALUES ('Jason Warne',
             101990,
             20,
             '10-AUG-09');

INSERT INTO xx_decode_test
     VALUES ('Glenn Symonds',
             101991,
             30,
             '11-SEP-11');

INSERT INTO xx_decode_test
     VALUES ('Ricky Manning',
             101992,
             40,
             '13-NOV-15');

INSERT INTO xx_decode_test
     VALUES ('Heath Flower',
             101993,
             50,
             '13-NOV-15');

INSERT INTO xx_decode_test
     VALUES ('George Brady',
             101994,
             10,
             '01-APR-03');

Once the table is created, run the following statement.

SELECT EMP_NAME,
       EMP_ID,
       DEPT_ID,
       DECODE (DEPT_ID,
               10, 'Marketing',
               20, 'Sales',
               30, 'Information Technology',
               40, 'Shipping')
           "Department Name"
  FROM xx_decode_test;

The query generates the following output.

Thus, can be seen that, the query checks for the expression – if the value is 10, the function gives the word “Marketing”, if the value is 20, the function gives back “Sales” and etc..

Also, in the example above, it can be seen that no Department Name is assigned for Dept_ID 50. And thus, it returns a NULL Value.