# Databases > Oracle Decode in SQL

## timmy

What is the purpose or usage of decode function in SQL. Someone brief me on the syntax also. If you could give an example for this function it would help me?

----------


## jamesravid

Here is syntax of DECODE function in Oracle

*DECODE(expr,search1,result1[,search2,result2]...[,default_val]);*

DECODE compares expr to each search(search1,search2...) value one by one. If expr is equal to a search1, then Oracle returns result1 else it compares expr to search2 and so on. If no match is found, then Oracle returns default_val.

the above decode function works exactly like the following if... else if...else statement. 

*if (expr = search1) then
  return result1
else if expr= search2) then 
  return result2
...
else
 return default_val;*


Example:

SELECT product_id,
       DECODE (id, 1, 'Southlake', 
                   2, 'San Francisco', 
                   3, 'New Jersey', 
                   4, 'Seattle',
                      'Non-domestic') 
       Location FROM inventris
       WHERE prod_id < 175;


This example decodes the value warehouse_id. If id is 1, then the function returns 'Southlake'; if warehouse_id is 2, then it returns 'San Francisco'; and so forth. If warehouse_id is not 1, 2, 3, or 4, then the function returns 'Non-domestic'.



I hope this helps  :Smile: .

----------


## nancyphilips

Thank you it was a nice explanation with a detailed example. Hope it helped many people in this discussion

----------


## sourisengupta

good explation.. keep it up

but the syntax and the example does not match..plz check.

----------

