How To Use TO_NUMBER Function In Oracle

How To Use TO_NUMBER Function In Oracle

October 16, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

Oracle To_Number data conversion allows to do explicit data conversions and that means in other words it forces your value to become a number type. You would need this function when numbers are stored into a text type for example as VARCHAR2. When you are using two different data type in a join condition or in Oracle Union operator there is a change the statements will fail and your SQL query will finish with an error. It is more safer to convert all values in explicit way to the same data type than to let Oracle do implicit data conversion and hope it does it in a correct way. The Oracle TO_NUMBER syntax is:

TO_NUMBER (<your_value>)

You are able to convert to number only the following other data types: CHAR, VARCHAR2, NCHAR, NVARCHAR2, BINARY_FLOAT and BINARY_DOUBLE.

The following example converts pseudocolumn Rownum to a number. The function Rownum is returning always number values and there shouldn’t be any difficulties to convert it to a number.

 SELECT TO_NUMBER (rownum)
   FROM dual;

dba oracle dba database management system database oracle performance tuning oracle to_number oracle to_number in oracle database programming database ORA-01722: invalid number VALUE_ERROR

The output above shows a number 1 since we do have only one row. In the next Oracle DBA example we are using the Dummy column from the Oracle Dual table. The column value is char “X” and that should quite difficult to turn a number. Take a look at the example below.

 SELECT TO_NUMBER (dummy)
   FROM dual;

dba oracle dba database management system database oracle performance tuning oracle to_number oracle to_number in oracle database programming database ORA-01722: invalid number VALUE_ERROR

As we did mention above the conversion to a number will be quite difficult or in other words impossible. The function couldn’t turn the “X” to a number and the SQL query raised error ORA-01722: invalid number. You can only convert number values saved a text or other data types to number types.

The third Select statement is showing the data we are going to use in our next example. The query returns 10 lines and every 4th line returns the “X” char from the Dummy column.

 SELECT DECODE (MOD (rownum,4),0,dummy,rownum) AS ID
   FROM dual
    CONNECT BY rownum < 11;

dba oracle dba database management system database oracle performance tuning oracle to_number oracle to_number in oracle database programming database ORA-01722: invalid number VALUE_ERROR

This example below should show us what happens with the SQL query that has mixed value types and we are trying convert them all to numbers. To see the original query lines take a look at the last example above.

 SELECT TO_NUMBER (qry.ID)
   FROM
    (SELECT DECODE (MOD (rownum,4),0,dummy,rownum) AS ID
       FROM dual
        CONNECT BY rownum < 11
    ) qry;

dba oracle dba database management system database oracle performance tuning oracle to_number oracle to_number in oracle database programming database ORA-01722: invalid number VALUE_ERROR

The Oracle Select returned error ORA-01722: invalid number again. This is not a common practice, but sometimes the data does come from other sources in a mixed way and we would need to take out only number values when they do exist. To do so our Oracle DBA has written a short function.

 CREATE FUNCTION get_only_numbers (P_TEXT VARCHAR2)
  RETURN NUMBER
 IS
 BEGIN
   RETURN TO_NUMBER(P_TEXT);

 EXCEPTION when VALUE_ERROR
           then RETURN null;
 END get_only_numbers;

dba oracle dba database management system database oracle performance tuning oracle to_number oracle to_number in oracle database programming database ORA-01722: invalid number VALUE_ERROR

The customised Oracle function has input text variable named P_TEXT and it returns only number values. Inside the function we are trying to do conversion with Oracle function TO_NUMBER and when it fails the function raises the build-in VALUE_ERROR error. The last part of function the VALUE_ERROR error will be caught by the EXCEPTION when clause and it returns NULL (empty) value. This function allows us to filter out only number values and to avoid the ORA-01722: invalid number error. Now has left to try the function with the Select statement.

 SELECT get_only_numbers(qry.ID) only_numbers
   FROM
    (SELECT DECODE (MOD (rownum,4),0,dummy,rownum) AS ID
       FROM dual
        CONNECT BY rownum < 11
    ) qry;

dba oracle dba database management system database oracle performance tuning oracle to_number oracle to_number in oracle database programming database ORA-01722: invalid number VALUE_ERROR

The SQL query above returned only numbers and works without raising any error. As we did already mentioned above this function is not very common practice but sometimes we do have do some extreme things and this Oracle function will help you out with this type of problem.

See Also:
Oracle Home

Leave a Reply

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

*