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.
Format Modes of Converted Strings
The number format of the returned query results can differ depending on whether you use Logical mode, ODBC mode, or Display mode.
Unless the string expression is a DOUBLE, the TO_NUMBER function returns a number of type NUMERIC. The NUMERIC data type has a default scale of 2. Therefore, when running queries in Display mode, InterSystems SQL displays the returned results with 2 decimal places.
SELECT TO_NUMBER('-15 degrees F') -- Display Mode: -15.00
Additional fractional digits are rounded to two decimal places.
SELECT TO_NUMBER('-15.835 degrees F') -- Display Mode: -15.84
Trailing zeros are also resolved to two decimal places.
SELECT TO_NUMBER('-15.60000 degrees F') -- Display Mode: -15.60
When TO_NUMBER is used via a database driver, it also returns the type as NUMERIC with a scale of 2. In Logical mode or ODBC mode, the returned value is a canonical number, no scale is imposed on fractional digits, and trailing zeros are omitted.
SELECT TO_NUMBER('-15 degrees F') -- Logical/ODBC Mode: -15
SELECT TO_NUMBER('-15.835 degrees F') -- Logical/ODBC Mode: -15.835
SELECT TO_NUMBER('-15.60000 degrees F') -- Logical/ODBC Mode: -15.6
If the input string expression is of data type DOUBLE, then TO_NUMBER also returns the value as data type DOUBLE. All format modes display the full precision of the converted number.
SELECT TO_NUMBER(CAST('-15.6 degrees F' AS DOUBLE)) -- -15.599999999999999644