How-to: decode

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

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


   SELECT decode(emp_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)

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.

“They told me that to make her fall in love I had to make her laugh. And everytime she laughs I’m the one who falls in love” ~ Tommaso Ferraris


Oracle SQL Functions
CASE function

Copyright © 1999-2023
Some rights reserved