CODEWITHSUNDEEP
sqloracle-databasecastingvarchar2
Jefferson
Jefferson

Reputation: 1

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

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:

  1. 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

Upvotes: 0

Views: 566

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

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';

db<>fiddle demo

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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)

Upvotes: 1

Related Questions