TO_NUMBER
Synopsis
TO_NUMBER(string-expression) TONUMBER(string-expression)
Arguments
Argument | Description |
---|---|
string-expression | The string expression to be converted. The expression can be the name of a column, a string literal, or the result of another function, where the underlying data type is of type CHAR or VARCHAR2. |
Description
The names TO_NUMBER and TONUMBER are interchangeable. They are supported for Oracle compatibility.
TO_NUMBER converts string-expression to a number of data type NUMERIC. However, if string-expression is of data type DOUBLE, TO_NUMBER returns a number of data type DOUBLE.
TO_NUMBER conversion takes a numeric string and converts it to a canonical number by resolving plus and minus signs, expanding exponential notation ("E" or "e"), and removing leading zeros. TO_NUMBER halts conversion when it encounters a nonnumeric character (such as a letter or a numeric group separator). Thus the string '7dwarves' converts to 7. If the first character of string-expression is a nonnumeric string, TO_NUMBER returns 0. If string-expression is an empty string (''), TO_NUMBER returns 0. TO_NUMBER resolves -0 to 0. TO_NUMBER does not resolve arithmetic operations. Thus the string '2+4' converts to 2. If NULL is specified for string-expression, TO_NUMBER returns null.
The NUMERIC data type has a default SCALE of 2. Therefore, when selecting this value in DISPLAY mode, TO_NUMBER always displays the return value with 2 decimal places. Additional fractional digits are rounded to two decimal places; trailing zeros are resolved to two decimal places. When TO_NUMBER is used via xDBC, 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.
Related SQL Functions
-
TO_NUMBER converts a string to a number of data type NUMERIC.
-
TO_CHAR performs the reverse operation; it converts a number to a string.
-
CAST and CONVERT can be used to convert a string to a number of any data type. For example, you can convert a string to a number of data type INTEGER.
-
TO_DATE converts a formatted date string to a date integer.
-
TO_TIMESTAMP converts a formatted date and time string to a standard timestamp.
Examples
The following two examples show how TO_NUMBER converts a string to a number, then returns it as data type NUMERIC with appropriate SCALE. The first example returns the number in Display mode, the second example returns the number in Logical mode:
ZNSPACE "SAMPLES"
SET myquery = "SELECT TO_NUMBER('+-+-0123.0093degrees')"
SET tStatement = ##class(%SQL.Statement).%New()
SET tStatement.%SelectMode=2
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display() // Display mode value: 123.01
ZNSPACE "SAMPLES"
SET myquery = "SELECT TO_NUMBER('+-+-0123.0093degrees')"
SET tStatement = ##class(%SQL.Statement).%New()
SET tStatement.%SelectMode=0
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display() // Logical mode value: 123.0093
The following examples show that when string-expression is of data type DOUBLE, TO_NUMBER returns the value as data type DOUBLE:
ZNSPACE "SAMPLES"
SET myquery = "SELECT TO_NUMBER(CAST('+-+-0123.0093degrees' AS DOUBLE))"
SET tStatement = ##class(%SQL.Statement).%New()
SET tStatement.%SelectMode=2
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display() // Display mode value
ZNSPACE "SAMPLES"
SET myquery = "SELECT TO_NUMBER(CAST('+-+-0123.0093degrees' AS DOUBLE))"
SET tStatement = ##class(%SQL.Statement).%New()
SET tStatement.%SelectMode=0
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display() // Logical mode value
The following example shows how to use TO_NUMBER to list street addresses ordered in ascending numerical order:
SELECT Home_Street,Name
FROM Sample.Person
ORDER BY TO_NUMBER(Home_Street)
Compare the results with the same data ordered in ascending string order:
SELECT Home_Street,Name
FROM Sample.Person
ORDER BY Home_Street