Oracle Database 12c: SQL Workshop I: 05 Using Conversion Functions and Conditional Expressions

Using Conversion Functions and Conditional Expressions

Conversion Functions

In some cases, the Oracle server receives data of one data type where it expects data of a different data type. When this happens, the Oracle server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by the Oracle server or explicitly by the user.

Implicit Data Type Conversion

Explicit Data Type Conversion

TO_CHAR

SQL> select to_char(sysdate,'fmDdspth "of" Month YYYY fmHH:MI:SS AM') from dual;

TO_CHAR(SYSDATE,'FMDDSPTH"OF"MONTHYYYYFMHH:MI:SSAM')
--------------------------------------------------------------------------------
Thirty-First of August 2018 11:10:27 PM

SQL> select to_char(10002,'L99G999D99') from dual;

TO_CHAR(10002,'L99G999D99')
----------------------------------------
      $10,002.00

TO_NUMBER
TO_DATE

SQL> select to_number('123123.123','999999.000') from dual;

TO_NUMBER('123123.123','999999.000')
------------------------------------
              123123.123

SQL> select TO_DATE('May 24, 1999', 'fxMonth DD, YYYY') from dual;

TO_DATE('MAY24,1999
-------------------
1999-05-24 00:00:00

General Functions
These functions work with any data type and pertain to the use of null values in the expression list.

NVL (expr1, expr2)

Syntax
NVL (expr1, expr2)
In the syntax:
• expr1 is the source value or expression that may contain a null
• expr2 is the target value for converting the null
You can use the NVL function with any data type, but the return value is always the same as the data type of expr1
Data types must match:
– NVL(commission_pct,0)
– NVL(hire_date,’01-JAN-97’)
– NVL(job_id,’No Job Yet’)

SQL> desc t4
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                         NUMBER
 NO                         NUMBER

SQL> select * from t4;

    ID     NO
---------- ----------
     2
     1

SQL> select nvl(no,2) from t4;

 NVL(NO,2)
----------
     2
     2

SQL> select nvl(no,'2') from t4;

NVL(NO,'2')
-----------
      2
      2

SQL> select nvl(no,'a') from t4;
select nvl(no,'a') from t4
              *
ERROR at line 1:
ORA-01722: invalid number

NVL2(expr1, expr2, expr3)

Syntax
NVL2(expr1, expr2, expr3)
In the syntax:
• expr1 is the source value or expression that may contain a null
• expr2 is the value that is returned if expr1 is not null
• expr3 is the value that is returned if expr1 is null
Note: The argument expr1 can have any data type. The arguments expr2 and expr3 can have any data types except LONG.

SQL> select * from t4;

    ID     NO
---------- ----------
     2
     1      1

SQL> select id,nvl2(no,1,0) from t4;

    ID NVL2(NO,1,0)
---------- ------------
     2        0
     1        1

SQL> select id,nvl2(no,1,'a') from t4;
select id,nvl2(no,1,'a') from t4
                    *
ERROR at line 1:
ORA-01722: invalid number

SQL> select id,nvl2(no,'a',0) from t4;

    ID NV
---------- --
     2 0
     1 a

NULLIF (expr1, expr2)

Syntax
NULLIF (expr1, expr2)
In the syntax:
• NULLIF compares expr1 and expr2. If they are equal, the function returns null. If they are not, the function returns expr1. However, you cannot specify the literal NULL for expr1

SQL> select * from t4;

    ID     NO
---------- ----------
     2
            3
     1      1

SQL> select nullif(id,no) from t4;

NULLIF(ID,NO)
-------------
        2

COALESCE (expr1, expr2, … exprn)

Syntax
COALESCE (expr1, expr2, … exprn)
In the syntax:
• expr1 returns this expression if it is not null
• expr2 returns this expression if the first expression is null and this expression is not null
• exprn returns this expression if the preceding expressions are null
Note that all expressions must be of the same data type.

Conditional Expression
CASE Expressions

CASE expr WHEN comparison_expr1 THEN return_expr1
         [WHEN comparison_expr2 THEN return_expr2
          WHEN comparison_exprn THEN return_exprn
          ELSE else_expr]
END

In a simple CASE expression, the Oracle server searches for the first WHEN … THEN pair for which expr is equal to comparison_expr and returns return_expr. If none of the WHEN … THEN pairs meet this condition, and if an ELSE clause exists, the Oracle server returns else_expr. Otherwise, the Oracle server returns a null. You cannot specify the literal NULL for all the return_exprs and the else_expr.
The expressions expr and comparison_expr must be of the same data type, which can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2,NUMBER,BINARY_FLOAT,or BINARY_DOUBLE or must all have a numeric datatype. All of the return values (return_expr) must be of the same data type.
If all expressions have a numeric datatype, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

DECODE Functions

DECODE(col|expression, search1, result1
                    [, search2, result2,...,]
                    [, default])

The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic that is used in various languages. The DECODE function decodes expression after comparing it to each search value. If the expression is the same as search, result is returned.
If the default value is omitted, a null value is returned where a search value does not match any of the result values.