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.

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

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

Python if , elif and else

Python Conditions and If statements

  • 0
Python for beginners

Learning Python Part 1

  • 3
Struct Alignment and Padding

Struct Alignment and Padding in C++ And C

  • 0
Friend function

Friend function C++

  • 0

C++ Pointers

  • 0

C++ Structures

  • 0
Types of Inheritance in C++

Inheritance and access specifiers C++

  • 0
Java date pattern

Java Date Pattern Syntax

  • 0
Java Date and Calendar

Java Date formats

  • 0
JAVA Data Type

Data types in Java

  • 0
Java unreachable code

Unreachable Code Error in Java

  • 0

Post Comment





Forgot Password

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