If the first search result data type is char at that time return value is converted to varchar2.

If the first search result is null then its return value is converted to varchar2 data type.Decode function returns the value that has the same data type as the first search result from the list.In a similar way we can implement different examples of decode function, suppose users need to compare the two different dates at that time we can use decode function for example if date1 > date2 in this example decode function returns date2 otherwise decode function should return date1.In the above statement the decode function compares each college_id value one by one as shown in the above statement.Now let’s see how it is equivalent to if – then – else statement as follows. When we execute the above query then final output we illustrate by using the following snapshot.Here we compare the college_id and college_name and we compare the first search value with the next search value. In the above example after inserting records into the college table we apply the decode function on the college table as shown in the above statement.When we execute the above query then final output we illustrate by using the following snapshot. Note here both argument values are equal then the decode function in Oracle returns the second argument value which is the string ‘Two’.This is a very simple example of decode function, in which the Oracle decode function compares the first search argument value with the second search argument value.Given below is the example of Oracle decode: Oracle decode function converts actual expression and search value to the data type of first search value before the comparison and it converts the return value to first search result data type. If the previous search result is equal to the actual expression at that time evaluation is terminated. It evaluates the search value before the comparison to the actual expression value rather than all search values. Short – circuit – evaluation is used in Oracle database. At that time oracle decode function returned string is varchar2 data types and it is the same character set as the first search result parameter. If search value, actual expression and the result can be any one of the data types such as char, varchar2, nchar or nvarchar. If actual expression and search value are the characters at that time oracle decodes function compare them by using the non padded comparison semantics. In which if the first search pair is the numeric at that time oracle decode function compares all search result expressions and in first search expression it finds which value is the highest numeric precedence and remaining argument convert to that data type and decode function returns the particular data type. It is used as a number, binary_float or binary_double. Now let’s see how the decode function works in Oracle:īasically decode function is used to find any match value by using if – then – else statement.ĭifferent types of arguments that we use in Oracle decode function are as follows: 1.

default: This is optional keyword, if there is no matches are found the decode function oracle returns the default value, if sometimes default value is omitted then it returns the null value (if decode function does not found any match value).

search result: It is used to display the search result that means if actual expression is equal to search value then it returns the result.

Similarly here all search values are automatically converted to the data type that is used for the first search value before comparing.

search value: This is the value we compared with actual expression.

actual expression: Is value to compare, it is automatically converted to data type that is used for first search value before comparing.

In above syntax we used decode function.

Decode oracle