Data Manipulation   «Prev  Next»
 Lesson 8 DECODE and NVL functions Objective Modify query results using the DECODE and NVL functions.

# DECODE Function in Oracle

## DECODE and NVL Functions

In programming and logic, a common construction of a problem is in the pattern
```if (temp >32)
then
[ice is melting]
else
[ice is solid]
```

DECODE follows this kind of logic. Basic structure and usage of DECODE.
```DECODE(value, if1, then1, if2, then2, if3, then3, . . . ,else)
```

The `DECODE` and `NVL` functions are really special. They can be used for date, number, and character datatypes.
I have found these two functions to be great time-savers when writing logic using Oracle SQL.

### DECODE

Think of `DECODE` as a mini-`IF`-statement, which can compare the value in a column or expression to a list of values and substitute a different value for each item on the list. The basic syntax is:

```DECODE(col1,case1,value1 [, case2, value2,...] ,
defaultvalue)
```

Add as many case and value pairs as you need. The final value is used when col1's value does not match any of the cases.
The MouseOver below shows an example of `DECODE`. Mouseover the state of Hawaii HI.

Oracle Decode Example
You can also use `DECODE` to create columns of summarized data in a single query.

### NVL

`NVL` is a simple function that replaces a null value with the specified value. It is useful when you want to perform arithmetic on columns that might contain null values. In these cases, a null value will cause the arithmetic to result in a null value. To avoid this, you can substitute zero for null value using the `NVL` function. Values that are anything other than null remain unchanged. The syntax is shown below.

### Decode Nvl Functions - Exercise

Click the Exercise link below to write a query using `DECODE` and `NVL`.
Decode NVL Functions - Exercise
The next lesson concludes this module.