Search code examples
oracleparsingnumber-formatting

How to convert char to number with mask

I can't convert char symbols with dot to number. I get exception

ORA-01481 invalid format mask

Which mask would work for me? I need unlimited number of characters before the dot. The star or n symbol doesn't work

select to_number('840.11', '*.99') from dual
Solution

  • The behaviour of to_number depends on database or even session settings, so in order to be sure to convert it the way you need, you need to supply the numeric characters, like this:

    select to_number('840.11', '999.99', ' NLS_NUMERIC_CHARACTERS = ''. ''') from dual

    Now if you have a higher number of digits before the dot, you can simply enlarge the format mask, like this:

    select to_number('234234840.11', '99999999999999.99', ' NLS_NUMERIC_CHARACTERS = ''. ''')
from dual

    There is no direct way to specify a dynamic amount of digits before the dot, but there is some kind of workaround described here: Dynamic length on number format in to_number Oracle SQL