Skip to main content

TO_NUMBER

A string function that converts a string expression to a value of NUMERIC data type.

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

See Also

FeedbackOpens in a new tab