How To Convert Character Strings to Numeric Values?

Answer

You can convert character strings to numeric values by using the CAST(string AS DECIMAL) or CAST(string AS SIGNED INTEGER) function as shown in the following examples:

SELECT CAST('4123.45700' AS DECIMAL) FROM DUAL;
4123.46
-- Very poor conversion

SELECT CAST('4.12345700e+3' AS DECIMAL) FROM DUAL;
4123.46
-- Very poor conversion

SELECT CAST('4123.45700' AS SIGNED INTEGER) FROM DUAL;
4123

SELECT CAST('4.12345700e+3' AS SIGNED INTEGER) FROM DUAL;
4
-- Very poor conversion