CAST(VARCHAR2 AS NUMERIC) with "." in the VARCHAR

Question

I have a problem when converting VARCHAR2 to Number in Oracle SQL.

My VARCHAR2 has the following characteristic: 0.000

For example: the value of Campo1 is 18.123 (with a .)

But I get an error:

00000 - "invalid number"

when trying to convert:

SELECT SUM(CAST(Campo1 AS NUMERIC)) AS VOLUME
FROM TESTE_NUMBER
WHERE Timestamp = '18/09/2020'

Is it possible to convert that string?

Thanks

Are you able to use [to_number](https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions191.htm)? — Jeffrey Drake, Sep 21 '20 at 16:10

score 1 · Answer 1 · answered Sep 21 '20 at 15:15

1

You can check that it looks like a number, first. For a non-negative:

select sum(case when regexp_like(campo1, '^[0-9]+[.]?[0-9]*$')
                the to_number(campo1)
           end)

answered Sep 21 '20 at 15:15

Gordon Linoff

I would use [VALIDATE_CONVERSION](https://stackoverflow.com/a/45886915/5070879) here :) – Lukasz Szozda Sep 21 '20 at 15:22

Lukasz Szozda · Answer 2 · 2020-09-21T15:20:27.237

0

Yes, if you remove space:

SELECT SUM(CAST(REPLACE(Campo1, ' ') AS NUMERIC)) AS VOLUME
FROM TESTE_NUMBER
where Timestamp = '18/09/2020';

'1722. 0000' should be '1722.0000'.

Using safe conversion “Safe” TO_NUMBER() will default faulty values to 0:

SELECT SUM(CAST(Campo1 AS NUMERIC DEFAULT 0 ON CONVERSION ERROR)) AS VOLUME
FROM TESTE_NUMBER
where Timestamp = '18/09/2020';

edited Sep 21 '20 at 15:20

answered Sep 21 '20 at 15:14

Lukasz Szozda

Thanks for answering me, Lukasz. But continues with the same error inserting the select that you wrote :/ – Jefferson Sep 21 '20 at 15:41

2 Answers2