If there are no matches, the DECODE function will return default and if default is omitted, then the function will return NULL. The value that is returned, if expression=search. The value that is compared against the expression. It automatically gets converted to the data type of the first search value before comparing. Since they are equal, the function returns the second argument which is the string ‘One’.ĭECODE(expression, search, result … ) In the following example, the Oracle DECODE() function compares the first argument with the second argument. Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i The DECODE function can be used in the following versions of Oracle or PLSQL: This includes the expression, search, and result arguments.

The maximum number of components that can be contained in the DECODE function is 255. If expr is null, then Oracle returns NULL which is the result of the first search. Two nulls are considered to be equivalent by Oracle. And converts the return value to the same data type as the first result.Įxample: If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the data type VARCHAR2. Oracle converts expr and search values to the datatype of the first search value before comparison. If a previous search is equal to expression, the evaluation is terminated. It evaluates search values only before comparing it to expression rather than evaluating all search values. Oracle Database uses short-circuit evaluation. The returned String is of VARCHAR2 datatype and is in the same character set as the first result parameter. expr, search, and the result can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. If expr and search are character data, then Oracle compares them using non-padded comparison semantics. If the first search-result pair is numeric, then Oracle compares all search-result expressions and the first expr to find the argument with the highest numeric precedence, converts the remaining arguments implicitly to that datatype, and returns that particular datatype.

Numeric types (NUMBER, BINARY_FLOAT, or BINARY_DOUBLE).

If default is omitted, then Oracle returns null. If a match is not found, then default is returned. If expression is equal to a search, then the corresponding result is returned by the Oracle Database. DECODE compares the expression to each search value one by one.

In Oracle, DECODE function allows us to add procedural if-then-else logic to the query. The topics which are going to be covered are: We will be learning the various ways to use DECODE, its syntax and understand it with examples. In this blog, we will try to get a complete understanding of DECODE function in SQL.

Oracle plsql decode