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.

DECODE

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.

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

COALESCE

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;

NVL

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;

TO_CHAR

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", 
TO_CHAR(1234) "TO_CHAR_NUMBER"
from dual;

TO_DATE

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.

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

TO_TIMESTAMP

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"
   FROM DUAL;

TO_NUMBER

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.

SELECT TO_NUMBER('10') + TO_NUMBER('5') "TO_NUMBER"
   FROM DUAL;

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