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

CodePudding user response：

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

fiddle