Try it here

Decode Function in Oracle


DECODE Function in Oracle

Decode function in oracle is similar to if else statement.

DECODE( expression , search , result [, search , result]... [, default] )

Expression: The value to compare. Data type is automatically converted into first search value before comparing.

Search : The value which is compared against Expression.

Result: The value which is returned if the expression matches with search values.

Default: If not match is found, default value is returned. If default is not specified then null is returned.

select decode(1,1,'ONE',2,'TWO') from dual;

Output: ONE

select decode(2,1,'ONE','2',2) from dual;

Output : 2

Below throws ORA-01722: invalid number error as 'TWO' cannot be converted into Number.

select decode(2,1,'ONE','TWO',2) from dual;

Search, result and default values can be derived from expression.Oracle uses short-circut evaluation which means database evaluates search value only before comparing it to expression. Database does not evaluate all search values before comparing any of them with expression.

select decode(2,'ONE','ONE','TWO',2,'No match') from dual;

Output : No match

Expr : 2 is implicitly converted into Character '2' . It does not matches with 'TWO' hence result is No match.

Decode considers 2 Null value to be equivalent.

select decode(NULL,null,'EQUAL','NOT EQUAL') from dual;

Output: EQUAL

Decode can also be used in Group by and Order by clause. Decode can also be used for greater than or less than comparison. Stay tuned for more information on decode.

Writer profile pic

Jay on Sep 21, 2019 at 12:03 am

This article is contributed by Jay. If you like and would like to contribute, you can write your article here or mail your article to . See your article appearing on the main page and help others to learn.

Post Comment

Comments( 0)




Forgot Password

Please enter your email address below and we will send you information to change your password.