TO_NUMBER function inside AVG doesn't work

Question

I'm in trouble with TO_NUMBER ORACLE function.

The query

SELECT TO_NUMBER(varchar2_column)
FROM TABLE@ANOTHER_DB;

works, but if I put TO_NUMBER inside AVG, ORACLE returns the following error:

ORA-01722: invalid number ORA-02063: preceding line from ANOTHER_DB Position: 0

The query is the following:

SELECT AVG(TO_NUMBER(varchar2_column))
FROM TABLE@ANOTHER_DB;

Could someone help me? Thanks in advance

The thing is, that there are non numerical data in the remote table. You don't get the error because the normal select statements just fetches some records and not the hole table. The question is, what you want to do with the invalid data... — Radagast81
Is there a where clause in your actual query? Are you fetching all the data when you run the query with just a to_number? Or only the first few pages of data? Is table@another_db actually a table? Or a view? Is there non-numeric data in the column? If so, how do you want to handle averaging numbers with, say, the string "abc"? Treat it as NULL? 0? Something else? — Justin Cave
That error is due to the fact that some of the data in that column cannot be converted to number. — wundermahn
If you have an actual db release you can try SELECT AVG(TO_NUMBER(varchar2_column DEFAULT NULL ON CONVERSION ERROR)) FROM TABLE@ANOTHER_DB — Radagast81
I read that NULL values are skipped by AVG ...and I know that in the table could be values like '90.0', are this values valid? — pairon

Radagast81 Radagast81 · Accepted Answer · 2019-09-24T14:04:45

You can use to following query to get a working format mask for your data:

SELECT AVG(TO_NUMBER(varchar2_column, 'FM'||REGEXP_REPLACE(varchar2_column,'\d','0'))) 
  FROM TABLE@ANOTHER_DB;

That will produce a formatmask replacing all numerical characters with 0, which should work with all your data.