REPLACE WITH NVL

Question

select to_number(replace(nvl('-100,000',0),'',','),'999,999,999.99')from dual;

... produces the output: 100000

Now I use the same expression in my procedure with table column:

select to_number(replace(nvl(TABLEA.VOLUME,0),'',','),'999,999,999.99')from TABLEA;

... and get the output: INVALID NUMBER

Column VOLUME in TABLEA is of type VARCHAR2.

This means you have some values in TABLEA.VOLUME which cannot be cast to numbers. — APC, Jul 18 '19 at 08:31
You are getting the error as all the values in `TABLEA` might not be a number format that you are expecting. — Popeye, Jul 18 '19 at 08:36

score 1 · Answer 1 · answered Jul 18 '19 at 08:31

1

Most certainly the contents of column VOLUME are stored as strings without the grouping character ( , ). Thus the strings violate the format in the conversion with to_number.

In addition, the argument order in REPLACE makes no sense.

Finally, you want to swap the calls to NVL and REPLACE.

So - assuming that you want to parse numbers with 2 fractional digits given as strings - use the following:

select to_number ( nvl ( replace (TABLEA.VOLUME, ',', ''), 0), '999999999.99') from TABLEA;

answered Jul 18 '19 at 08:31

collapsar

17,010
4
35
61

select to_number ( nvl ( replace (10,000, ',', ''), 0), '999999999.99') from DUAL; --it's not working – aishwarya m Jul 18 '19 at 09:25
ORA-00939-TOO MAY ARGUMENTS FOR FUNCTION – aishwarya m Jul 18 '19 at 09:26
@aishwaryam Yes, your syntax is wrong: The first argument to `replace` should be a string and if you want to exploit auto-conversion from number then don't use a grouping character (`,`) in your number. ( see it yourself: `select to_number ( nvl ( replace ('10,000', ',', ''), 0), '999999999.99') from DUAL;` works) – collapsar Jul 18 '19 at 09:27

Barbaros Özhan · Answer 2 · 2019-07-18T16:42:21.097

Seems you want to convert the string values to money format.

First, consider replace as you did by switching '' and ',' parameters in the argument order to remove the commas, and making suitable for numeric conversion as :

to_number(replace(nvl(str,0),',','')),

and then apply the money format as :

with tableA as
(
 select '-100,000' as volume
   from dual
)
select to_char( 
                to_number(replace(nvl(volume,0),',','')) 
              ,'fm999G999G990D00','NLS_NUMERIC_CHARACTERS = ''.,''')
       as Money
  from tableA;


MONEY
-----------
-100,000.00

Depending on the currency displaying style the order of dot and comma can be switched as 'NLS_NUMERIC_CHARACTERS = '',.''' to display the money as -100.000,00.

P.S. first of all, you need to make sure all the data for volume column can be casted to number to be able to use to_number() conversion.

Demo

REPLACE WITH NVL

2 Answers2