Problem solve Get help with specific problems with your technologies, process and projects.

What does the decode function do, and how is it structured?

What does the decode function do, and how is it structured?

DECODE is an Oracle function that is best understood by an example

DECODE(mth,1,'Jan', 2,'Feb', 3,'Mar'
         , 4,'Apr', 5,'May', 6,'Jun'
         , 7,'Jul', 8,'Aug', 9,'Sep'
        , 10,'Oct',11,'Nov',12,'Dec'
           , 'Err')
    as mthname

Whatever value is in the "mth" column is searched in the list of values and if found, is decoded to the expression provided, and if not found, the default value is the result. In the example above, the month number is translated into a month name, and if for some reason the "mth" column has a value that is not in the list, the default 'Err' month name is supplied.

DECODE does not have to be used only in one-for-one translations. It is also useful for aggregations.

         ,10,'Fall' ,11,'Fall' ,12,'Fall'
    as season

Especially when incorporated into a database View, DECODE is valuable in avoiding unnecessary coding in applications that require the decoded expressions. The equivalent in standard SQL would be the CASE structure, which is more powerful as it allows range tests.

For More Information

This was last published in April 2001

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.