String to double or decimal

Question

How to convert a string to a double or decimal? In Exact Online (REST API) I try to calculate with a decimal value in a string field. e.g items.netprice + items.notes. The field items.notes contains the decimal value. Tried using cast and convert in combination with float and decimal.

Answer 1

I would use a solution like:

select case
       when regexp_replace(c, '[^0-9.]', '', 1, 0, 'g') = c
       then to_number(c)
       else null
       end       
from   ( select '123.45' c
         from   dual@datadictionary
         union all
         select '123invalid.45' c
         from   dual@datadictionary
       )

The case with regexp_replace ensures that non-number are returned as null. You might want to change that to an error if deemed necessary.