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
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;