Caché SQL Reference
TO_NUMBER
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

A string function that converts a string expression to a value of NUMERIC data type.
Synopsis
TO_NUMBER(string-expression)

TONUMBER(string-expression)
Arguments
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
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
 
See Also