I have a table with numbers stored as varchar2
with '.' as decimal separator (e.g. '5.92843').
I want to calculate with these numbers using ',' as that is the system default and have used the following to_number
to do this:
TO_NUMBER(number,'99999D9999','NLS_NUMERIC_CHARACTERS = ''.,''')
My problem is that some numbers can be very long, as the field is VARCHAR2(100)
, and when it is longer than my defined format, my to_number
fails with a ORA-01722
.
Is there any way I can define a dynamic number format? I do not really care about the format as long as I can set my decimal character.
You can't have "unlimited" number. Maximum precision is 38 significant digits. From the documentation.
You might try one of the following approaches (take them for an idea as I do not have a DB for trying it here):
1) Use TO_NUMBER without a format. According to Oracle docs it uses a dot for decimal separator then.
If your number contains group separators, then first remove these and convert then:
2) Generate the number format from your input:
The latter translates all digits to 9, your group character (here: comma) and your decimal separator (here: dot) to those used by Oracle by default.
The only way, is to set the appropriate value for
nls_numeric_characters
parameter session wide and useto_number()
function without specifying a format mask.Here is a simple example.Decimal separator character is comma
","
and numeric literals contain period"."
as decimal separator character: