CODEWITHSUNDEEP
oracle-databaseparsingnumber-formatting
Daler Khabibov
Daler Khabibov

Reputation: 25

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

Upvotes: 1

Views: 1284

Answers (2)

Thomas Tschernich
Thomas Tschernich

Reputation: 1282

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

Upvotes: 1

Radagast81
Radagast81

Reputation: 3006

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

Upvotes: 0

Related Questions