How can I convert this input in a number with Oracle

Question

I haven't been able to find the right conversion to a decimal number, from a field declared as varchar.

Oracle 12c

The source info said they set this format: 'S9999999999999V,99'

I used TO_NUMBER() function, but cant find the correct cast and arguments to read and convert properly the source.

With only TO_NUMBER(field) send and invalid number error.

Data received examples

1. +0000000000160,00
2. -9999999999999,99

I keep receiving the invalid number error.

Answer 1

The V in the source info format doesn't look right; from the documentation:

Element	Example	Description
V	999V99	Returns a value multiplied by 10n (and if necessary, round it up), where n is the number of 9's after the V.

If the rest of the model is right, and it seems be as it matches the two examples, then you probably want:

to_number(source, 'S9999999999999D99', 'NLS_NUMERIC_CHARACTERS=,.')

The fmt part is the same as you were told, just with V removed, and with D to mark the decimal separator; and the nls part says that the decimal separator is a comma.

-- CTE for sample data
with your_table (source) as (
  select '+0000000000160,00' from dual
  union all
  select '-9999999999999,99' from dual
)
-- query against sample data
select source,
  to_number(source, 'S9999999999999D99', 'NLS_NUMERIC_CHARACTERS=,.') as result
from your_table

gives

SOURCE            RESULT
----------------- -----------------
+0000000000160,00               160
-9999999999999,99 -9999999999999.99

The client will choose whether to display the converted value with a comma or period decimal separator, usually via NLS settings. You can change that session setting, or explicitly convert the number back to a string in a specific format.

db<>fiddle showing default and modified session output.