oracle how to manage scientific notation a thousands separator in same column

Question

i have an imported dataset with text column that i have to use as number and contains digit with many differents format.

letting oracle autoconvert text to number raise error if a thousand separator is found

i write a simple routine to test oracle conversion:

SELECT val, TRUNC(val, 0), MOD(val, 1) - 1
from (
    select '8E4' val from dual union
    select '8E-4' val from dual union
    select '1,234.567' val from dual union
    select '1.234' val from dual
);

is there a way to manage it? thanks

Answer 1

You can use a case expression to choose between two format models, for example:

to_number(val,
  case when instr(val, 'E') > 0 then '9EEEE' else '999G999G999D99999' end,
  'nls_numeric_characters=.,')

So for your example you could do:

select val, num, trunc(num, 0), mod(num, 1) - 1
from (
  select val,
    to_number(val,
      case when instr(val, 'E') > 0 then '9EEEE' else '999G999G999D99999' end,
      'nls_numeric_characters=.,') as num
  from (
      select '8E4' val from dual union
      select '8E-4' val from dual union
      select '1,234.567' val from dual union
      select '1.234' val from dual
  )
);

VAL	NUM	TRUNC(NUM,0)	MOD(NUM,1)-1
8E4	80000	80000	-1
8E-4	.0008	0	-.9992
1,234.567	1234.567	1234	-.433
1.234	1.234	1	-.766

fiddle