Oracle PL/SQL/PL SQL/Number to Char

Converting a character formatted as currency to an integer value

   <source lang="sql">

SQL> SQL> SQL> -- Converting a character formatted as currency to an integer value. SQL> DECLARE

 2        myString VARCHAR2(20) := "$123,456.78";
 3        myNumber NUMBER ;
 4  BEGIN
 5        myNumber := TO_Number(myString,"$999,999,999.99");
 6        DBMS_OUTPUT.PUT_LINE("The converted number is: " || myString);
 7        DBMS_OUTPUT.PUT_LINE("Your commission at 6% is: " || (myNumber*.06));
 8  END;
 9  /

The converted number is: $123,456.78 Your commission at 6% is: 7407.4068 PL/SQL procedure successfully completed. SQL> SQL>

      </source>
   
  

Converting Numbers to Characters Formatted as a Numeric String

   <source lang="sql">

SQL> SQL> DECLARE

 2       v_Convert_Number NUMBER := 90210;
 3       v_Hold_Char VARCHAR2(21) ;
 4  BEGIN
 5       v_Hold_Char := TO_CHAR(v_Convert_Number,"0000000000");
 6       DBMS_OUTPUT.PUT_LINE("The employee ID is: " || v_Hold_Char);
 7  END;
 8  /

The employee ID is: 0000090210 PL/SQL procedure successfully completed. SQL> SQL> SQL> --

</source>
   
  

Expressing Your Work in Scientific Notation

   <source lang="sql">

SQL> SQL> DECLARE

 2       v_Convert_Number NUMBER := 90210;
 3       v_Hold_Char VARCHAR2(21) ;
 4  BEGIN
 5       v_Hold_Char := TO_CHAR(v_Convert_Number,"9.99EEEE");
 6       DBMS_OUTPUT.PUT_LINE("The Scientific Notation is: " || v_Hold_Char);
 7  END;
 8  /

The Scientific Notation is: 9.02E+04 PL/SQL procedure successfully completed. SQL> SQL> SQL> --

</source>
   
  

Illegal conversion between character and number

   <source lang="sql">

SQL> SQL> DECLARE

 2    v_TempVar NUMBER;
 3  BEGIN
 4    v_TempVar := "xyz";
 5  END;
 6  /

DECLARE

ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 4

SQL> SQL>

</source>