decode

Return different values based on the content of a column/expression.

   decode(expression, search1, return1,
                      [search2, return2]...
                      [default] )

Examples

   SELECT decode(sex, 'M', 'Male',
                      'F', 'Female', 'Unknown')
   FROM   employees;

Return the Order_Date if the invoice_date is > Order_date otherwise return the invoice_date:

   SELECT DECODE(SIGN(Invoice_Date-Order_Date), 1, Order_Date, Invoice_Date)
   FROM ORDERS;

You can convert a character or expression that contains a number into an actual number value.

The 'format' must be a valid Number format.

nls_lang allows international formats to be applied e.g. currency symbols and numeric chars.

See Also

CASE function