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

Question

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

Answer 1

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;

Answer 2

Looks like you use wrong nls_numeric_characters values. Try to replace 'nls_numeric_characters=''.,''' with 'nls_numeric_characters='', '''

Explanation: Your nls_numeric_characters defined . as a decimal delimiter and , as a group delimiter, but, according to your example, you assume that the decimal delimiter is ,