TO_NUMBER Function

The TO_NUMBER function can convert a number or a character expression representing a number value to a DECIMAL data type.

The TO_NUMBER function has this syntax:

TO_NUMBER Function

1  TO_NUMBER (
2.1 char_expr
2.1 num_expr
1 )

Element Description Restrictions Syntax
char _expression Expression to be converted to a DECIMAL value Must be a literal, host variable, expression, or column of a character data type Expression
num_expression Expression that evaluates to a real number Must return a numeric data type Expression

The TO_NUMBER function converts its argument to a DECIMAL data type. The argument can be the character string representation of a number or a numeric expression.

The following example retrieves a DECIMAL value that the TO_NUMBER function returns from the literal representation of a MONEY value:
SELECT TO_NUMBER('$100.00') from mytab; 
The following table shows the output of this SELECT statement.
(expression)
100.000000000000

In this example, the currency symbol is discarded from the '$100.00' string.

The TO_NUMBER function is not required in most contexts, because by default, HCL OneDB™ converts numbers that include a decimal point (and quoted strings in the format of a literal number that has a decimal point) to a DECIMAL data type. This function can be useful, however, when you are migrating SQL applications that were originally written for other database servers, if the application makes calls to a function of this name that returns a DECIMAL value.