String-to-Number Conversion Operations
This example shows the different operations that TO_NUMBER performs to convert numeric strings into canonical numbers. The returned results shown in the SQL comments are in Logical mode. For more details on how converted numbers are displayed, see Format Modes of Converted Strings.
TO_NUMBER resolves leading plus and minus signs.
SELECT TO_NUMBER('-+123 feet') -- -123
SELECT TO_NUMBER('+-+-123 feet') -- 123
TO_NUMBER also expands exponential notation ("E" or "e").
SELECT TO_NUMBER('1e3') -- 1000
SELECT TO_NUMBER('1E-3') -- .001
TO_NUMBER halts conversion when it encounters a nonnumeric character, such as a letter or a numeric group separator.
SELECT TO_NUMBER('7dwarves') -- 7
If the first character of the string expression is not numeric, or if the expression is an empty string ('') or -0, TO_NUMBER returns 0.
SELECT TO_NUMBER('question3') -- 0
SELECT TO_NUMBER('') -- 0
SELECT TO_NUMBER('-0') -- 0
TO_NUMBER does not resolve arithmetic operations. For example, in this string, TO_NUMBER halts conversion at the "+" character and returns 2.
SELECT TO_NUMBER('2+4') -- 2
If NULL is specified for the string expression, TO_NUMBER returns null.