Oracle PL/SQL Tutorial/PL SQL Data Types/CHAR Type Convert

Converting a Character Formatted as Currency to an Integer Value

SQL>
SQL> set serveroutput on
SQL> set echo on
SQL>
SQL>
SQL> DECLARE
  2       v_Convert_Number VARCHAR2(20) := "$119,252.75";
  3       v_Hold_Number NUMBER ;
  4  BEGIN
  5       v_Hold_Number := TO_Number(v_Convert_Number,"$999,999,999.99");
  6       DBMS_OUTPUT.PUT_LINE("The converted number is: " || v_Hold_Number);
  7       DBMS_OUTPUT.PUT_LINE("Your commission at 6% is: " ||
  8                            (v_Hold_Number*.06));
  9  END;
 10  /
The converted number is: 119252.75
Your commission at 6% is: 7155.165
PL/SQL procedure successfully completed.

Converting a Character to an Integer Value

SQL>
SQL>
SQL> set serveroutput on
SQL> set echo on
SQL>
SQL> DECLARE
  2       v_Convert_Number VARCHAR2(20) := "1999";
  3       v_Hold_Number NUMBER ;
  4  BEGIN
  5       v_Hold_Number := TO_Number(v_Convert_Number,"9999");
  6       DBMS_OUTPUT.PUT_LINE("The converted number is: " || v_Hold_Number);
  7       DBMS_OUTPUT.PUT_LINE("The converted number plus 10 is: " ||
  8                            (v_Hold_Number+10));
  9  END;
 10  /
The converted number is: 1999
The converted number plus 10 is: 2009
PL/SQL procedure successfully completed.

Converting VARCHAR2 Percentage Data to a Decimal Equivalent

SQL>
SQL> set serveroutput on
SQL> set echo on
SQL>
SQL>
SQL> DECLARE
  2       v_Convert_Number VARCHAR2(20) := "33.33";
  3       v_Hold_Number NUMBER ;
  4  BEGIN
  5       v_Hold_Number := TO_Number(v_Convert_Number,"999.999999");
  6       DBMS_OUTPUT.PUT_LINE("The converted number is: " || v_Hold_Number);
  7       DBMS_OUTPUT.PUT_LINE("Your decimal equivalent is: " ||
  8                            (v_Hold_Number/100));
  9  END;
 10  /
The converted number is: 33.33
Your decimal equivalent is: .3333
PL/SQL procedure successfully completed.
SQL>

Convert Number to VARCHAR implicitly

SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    d1    DATE;
  3    cd1   VARCHAR2(10);
  4    cd2   VARCHAR2(10);
  5    n1    NUMBER;
  6    cn1   VARCHAR2(10);
  7    cn2   VARCHAR2(10);
  8  BEGIN
  9    cn1 := "995";
 10    n1 := cn1 + .99 ;
 11    cn2 := n1;
 12
 13    DBMS_OUTPUT.PUT_LINE("CN1 = " || cn1);
 14    DBMS_OUTPUT.PUT_LINE("N2 = " || n1);
 15    DBMS_OUTPUT.PUT_LINE("CN2 = " || cn2);
 16  END;
 17  /
CN1 = 995
N2 = 995.99
CN2 = 995.99
PL/SQL procedure successfully completed.
SQL>
SQL>

Convert VARCHAR to Number implicitly

SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    d1    DATE;
  3    cd1   VARCHAR2(10);
  4    cd2   VARCHAR2(10);
  5    n1    NUMBER;
  6    cn1   VARCHAR2(10);
  7    cn2   VARCHAR2(10);
  8  BEGIN
  9    cn1 := "995";
 10    n1 := cn1 + .99 ;
 11
 12    DBMS_OUTPUT.PUT_LINE("CN1 = " || cn1);
 13    DBMS_OUTPUT.PUT_LINE("N2 = " || n1);
 14  END;
 15  /
CN1 = 995
N2 = 995.99
PL/SQL procedure successfully completed.