TO_NUMBER(stringExpression) converts the input string expression to a canonical number of data type NUMERIC. If the string expression is of data type DOUBLE, TO_NUMBER returns a number of data type DOUBLE. All other types that do not appear in the following table return the type of stringExpression:
Type of stringExpression Type returned VARCHAR, VARBINARY, TIME NUMERIC BIT TINYINT DATE INTEGER TIMESTAMP POSIXTIME BIGINT
This query returns the addresses of a specific street in ascending numerical order. If you do not convert the street addresses by specifying ORDER BY Home_Street and do not convert to numbers, then the addresses follow string collation order (1, 10, 100, 2, 20, 200, and so on).
SELECT Name,Home_Street FROM Sample.Person WHERE Home_Street LIKE '%Oakhurst%' ORDER BY TO_NUMBER(Home_Street)
TONUMBER(stringExpression) is equivalent to TO_NUMBER(stringExpression).
The string expression to be converted. The expression can be the name of a column, a string literal, or the result of another function that has an underlying data type of CHAR or VARCHAR2.
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