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


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.