SQL Oracle - Clob to Number

Question

I have a table which does not have to be remodeled, it is composed by a CLOB field which has decimal values separated by point ex:(123.12)

I need this field to be returned to me in the result as a number, decimal, float or any type that is real numeric.

How do I do this in select ?

I’ve tried to do:

Select TO_NUMBER(TO_CHAR(CLOB)) FROM TABLE, for example.

Answer 1

The problem is the values with point and not comma, to convert to numeric value you need to do a REPLACE, Replacing . (dot) by , (comma)

Following example:

--CRIANDO TABELA TESTE
CREATE TABLE teste_clob(
campo_clob CLOB
);

--INSERINDO DADO TESTE
INSERT INTO teste_clob
(campo_clob)
VALUES
('123.12');

--VERIFICANDO VALOR CLOB
SELECT campo_clob FROM teste_clob;

--CONVERTENDO FORMATO EXPLICATIVO
SELECT TO_NUMBER(REPLACE(valor_char,'.',',')) AS valor_number
FROM(
SELECT CAST(campo_clob AS VARCHAR2(200)) AS valor_char 
FROM teste_clob)

--CONVERTENDO FORMATO SIMPLIFICADO
SELECT TO_NUMBER(REPLACE(campo_clob,'.',',')) AS RESULTADO 
FROM teste_clob;

Answer 2

Good afternoon, I thank everyone who answered, but after so much hitting my head I managed to solve my problem as follows. It’s not something beautiful, but it’s working as I expected.

TO_NUMBER(TO_CHAR(CAST(AMOUNT AS VARCHAR2(200)),'9999999999999999.999999999') AS AMOUNT,