PL/SQL Conversion/Decoding/NULL-Related Functions

Hi Folks,


Conversion functions convert a value from one datatype to another. Generally, the form of the function names follows the convention datatype TO datatype. The NULL-related functions facilitate null handling. The encoding and decoding functions let you inspect and decode data in the database.

The following list contains 7 of the most used conversion, decoding, and null-related functions.



Syntax: DECODE(expr, search, result, default)

Description: DECODE compares expr to each search value one by one. If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null.

The decode function is one of the most used functions in PL/SQL and plays a vital place in any system. It works like a group of if/else and you can add as many expressions as you need.

   v_var varchar2(20) := '';
  for i in 1..3
     select DECODE(i, 1, 'Line 1',
                      2, 'Line 2',
                      'Default Line')
            into v_var
     from dual;
  end loop;



Syntax: COALESCE(expr1, expr2, … exprn)

Description: COALESCE returns the first non-null expr in the expression list. At least one expr must not be the literal NULL. If all occurrences of expr evaluate to null, then the function returns null.

select name, name_ascii,  COALESCE(name_ascii, name) "name_ascii_2"
from city 
where name_ascii is null;



Syntax: NVL(expr1, expr2)

Description: If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

select name, name_ascii,  nvl(name_ascii, name) "NVL"
from city 
where name_ascii is null;



Syntax: TO_CHAR(param, fmt)

Description: TO_CHAR (param) converts any param as NCHAR, NVARCHAR2, CLOB, NCLOB, datetime, interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, NUMBER, BINARY_FLOAT, or BINARY_DOUBLE to a value of VARCHAR2 datatype, using the optional number format fmt.

select TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') "TO_CHAR_DATETIME", 
from dual;



Syntax: TO_DATE(char, fmt)

Description: TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype. The fmt is a datetime model format specifying the format of char. If you omit fmt, then char must be in the default date format. If fmt is J, for Julian, then char must be an integer.

    'April 27, 2020, 11:00 A.M.',
    'Month dd, YYYY, HH:MI A.M.',
     'NLS_DATE_LANGUAGE = American') "TO_DATE"



Syntax: TO_TIMESTAMP(char, fmt)

Description: TO_TIMESTAMP converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype.

SELECT TO_TIMESTAMP ('27-Apr-20 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') "TO_TIMESTAMP"



Syntax: TO_NUMBER(char)

Description: TO_NUMBER converts expr to a value of NUMBER datatype. The expr can be a BINARY_FLOAT or BINARY_DOUBLE value or a value of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype containing a number in the format specified by the optional format model fmt.



Please write down in the comments any further questions you may have. I’ll be happy to help you.


About Joel Medeiros

I am an experienced Business and Data Analyst, ETL and PL/SQL with over 13 years of experience. My technical skills are focus on Oracle mainly in developing with tools such as PL/SQL, ODI and RIB.You also can check all my post right here, feel free to keep in touch by e-mail or Linkedin at: [email protected] |

Leave a Reply

Your email address will not be published. Required fields are marked *