1

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
Mark Stewart
  • 2,046
  • 4
  • 22
  • 32
Daler Khabibov
  • 25
  • 6
  • If you want this result then why you are using a format model at all? you can directly convert it to number using -- `to_number('840.11')` trailing .99 in your format model will not have any effect. – Popeye Aug 08 '19 at 06:35
  • Because of decimal point character, @Tejash. Not everyone uses dot; some use comma. Your code raises an error in my database. – Littlefoot Aug 08 '19 at 06:38
  • 1
    You can set NLS_NUMERIC_CHARACTERS if you want different separator for number at database or session level according to your requirement. – Popeye Aug 08 '19 at 08:43

2 Answers2

1

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

Thomas Tschernich
  • 1,264
  • 15
  • 29
0

You can build a fitting formatmask using TRANSLATE and then ROUND (or TRUNC) the solution of the transformation to your needed accuracy:

SELECT ROUND(TO_NUMBER('840.1155',TRANSLATE('840.1155', '123456789', '000000000')),2) FROM dual

=> 840.12
Radagast81
  • 2,921
  • 1
  • 7
  • 21