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;

blank

 

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;

blank

 

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;

blank

 

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;

blank

 

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;

blank

 

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;

blank

 

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

 

 318 views last month,  3 views today

About Joel Medeiros

blank
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 https://ittutorial.org/author/joel-medeiros/ Please, feel free to keep in touch by e-mail or Linkedin at: joel.medeiros01@gmail.com | https://www.linkedin.com/in/joel-medeiros/

Check Also

blank

Alter System Flush Shared pool in Oracle

Hi, I will explain Alter System Flush Shared pool in Oracle in this post.  1,405 views …

Leave a Reply