sqloracle-databasetype-conversionoracle12cvarchar2

ORA-01722: invalid number rows start with comma transfer VARCHAR2 TO_NUMBER


I have following source data in VARCHAR2 format

,00100000004749745   
,100000001490116  
,125  
,200000002980232  
,25  
,439999997615814  
,5  
0  
1  
1,10000002384186  
1,5  
100  
2,1800000667572  
3   
3,29999995231628  
96  
999

What is the formula to transfer it to NUMBER?

With the following

INSERT INTO table_b.column_b
    SELECT 
        TO_NUMBER (column_a,'9999999999D9999999999999999999999',
        'nls_numeric_characters= ''.,''') as my_numbers
    FROM table_a.column_a;

I get an error

ORA-01722: invalid number error message.

I assume that it is because rows starting with comma e.g (,125).

In destination table I need in number format the data like this

0,00100000004749745   
0,100000001490116  
0,125  
0,200000002980232  
0,25 
0,439999997615814  
0,5 
0
1
...

Also tried to put zero '0' in front of comma and them change it to number with

Select 
    column_a,
    TO_NUMBER (column_a,'9999D9999999999999999999999',
   'nls_numeric_characters= ''.,''')  as my_number 
from 
    (SELECT DISTINCT 
         '0'|| column_a
     FROM table_a.column_a
     WHERE column_a LIKE (',125')
    );

but the result was

0,125   125

Solution

  • As Vasyl stated, your nls_numeric_characters needs to be adjusted. The query below demonstrates how to convert the string to a number.

    WITH
        my_numbers (column_a)
        AS
            (SELECT ',00100000004749745' FROM DUAL
             UNION ALL
             SELECT ',100000001490116' FROM DUAL
             UNION ALL
             SELECT ',125' FROM DUAL
             UNION ALL
             SELECT ',200000002980232' FROM DUAL
             UNION ALL
             SELECT ',25' FROM DUAL
             UNION ALL
             SELECT ',439999997615814' FROM DUAL
             UNION ALL
             SELECT ',5' FROM DUAL
             UNION ALL
             SELECT '0' FROM DUAL
             UNION ALL
             SELECT '1' FROM DUAL
             UNION ALL
             SELECT '1,10000002384186' FROM DUAL
             UNION ALL
             SELECT '1,5' FROM DUAL
             UNION ALL
             SELECT '100' FROM DUAL
             UNION ALL
             SELECT '2,1800000667572' FROM DUAL
             UNION ALL
             SELECT '3' FROM DUAL
             UNION ALL
             SELECT '3,29999995231628' FROM DUAL
             UNION ALL
             SELECT '96' FROM DUAL
             UNION ALL
             SELECT '999' FROM DUAL)
    SELECT n.column_a,
           TO_NUMBER (n.column_a,
                      '9999999999D9999999999999999999999999999',
                      'nls_numeric_characters= '', ''')    AS column_a_as_number
      FROM my_numbers n;