0

I have myfield as varchar2 type and I try to sum this field by using sum(to_number(myfield)) but the result is ORA-01722 invalid number.

before this error occured I used SUM(TO_NUMBER(REGEXP_REPLACE(BIKOU,'[[:alpha:]]', ''))) and it works but last week I put some decimal value in myfield so this code not work anymore.

Here is my example of data in myfield 10,12,13.5,NULL

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
Worrawan285
  • 21
  • 1
  • 2
  • Why do you need that regexp_replace? This means that 'X1X2X3X4XX' will be 1234, do you really want this? – Aleksej May 27 '16 at 08:48
  • What is you `nls_numeric_characters` set to? – Alex Poole May 27 '16 at 08:55
  • 1
    ***Never*** ever store numbers in `varchar` columns. Fix your table design instead. –  May 27 '16 at 09:01
  • I just want to sum the numeric value that store as varchar2 – Worrawan285 May 27 '16 at 09:41
  • The point is that if you used the right data type for your column you wouldn't need to use hacks like this in the first place. You need to be clear what you mean by numeric value though; as Aleksej said your code will treat any digits in a mixed string as a number, which might not be what you want; and does '13.5' count as numeric to you? What about '13,5' or '1,000.5' or '1.000,5'? – Alex Poole May 27 '16 at 09:56

1 Answers1

3

If you're getting that error from a string like 13.5 then your session's NLS_NUMERIC_CHARACTERS seems to be set to use a comma as the decimal separator:

alter session set nls_numeric_characters=',.';

with your_table (bikou) as (
  select '10' from dual
  union all select '12' from dual
  union all select '13.5' from dual
  union all select null from dual
)
select SUM(TO_NUMBER(REGEXP_REPLACE(BIKOU,'[[:alpha:]]', '')))
from your_table;

SQL Error: ORA-01722: invalid number

You can either explicitly set the session to use a period as the decimal separator, or provide a format mask that uses a period:

select SUM(TO_NUMBER(REGEXP_REPLACE(BIKOU,'[[:alpha:]]', ''), '99999999.99999'))
from your_table;

SUM(TO_NUMBER(REGEXP_REPLACE(BIKOU,'[[:
---------------------------------------
                                   35,5

Or use the decimal separator marker in the model and override the session's NLS setting:

select SUM(TO_NUMBER(REGEXP_REPLACE(BIKOU,'[[:alpha:]]', ''),
  '99999999D99999', 'nls_numeric_characters=''.,'''))
from your_table;

SUM(TO_NUMBER(REGEXP_REPLACE(BIKOU,'[[:
---------------------------------------
                                   35,5

The mask obviously has to be suitable for all the values you expect back from your regex; what I've used may not be quite right for your data.

This kind of issue is why you should not store numbers or dates as strings. Use the correct data type for your columns.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318