AS_DECIMAL , AS_NUMBER¶

Casts a VARIANT value to a fixed-point NUMBER value, with optional precision and scale. This function doesn’t cast floating-point values.

AS_DECIMAL is a synonym for AS_NUMBER.

The DECIMAL data type is synonymous with the NUMBER data type.

Syntax¶

AS_DECIMAL( <variant_expr> [ , <precision> [ , <scale> ] ] )

AS_NUMBER( <variant_expr> [ , <precision> [ , <scale> ] ] )

Copy

variant_expr

An expression that evaluates to a value of type VARIANT.

precision

The number of significant digits of the decimal number to store.

The default is 38.

scale

The number of significant digits after the decimal point.

The default is 0.

The function returns a value of type NUMBER or NULL:

If the type of the value in the variant_expr argument is DECIMAL or NUMBER, the function returns a value of type NUMBER.

If the type of the value in the variant_expr argument doesn’t match the type of the output value, the function returns NULL.
If the variant_expr argument is NULL, the function returns NULL.

When reducing scale, this function rounds the result, which can cause out-of-range errors.

Create a table and load data into it:

CREATE OR REPLACE TABLE as_number_example (number1 VARIANT);

INSERT INTO as_number_example (number1)
  SELECT TO_VARIANT(TO_NUMBER(2.34, 6, 3));

Copy

Use the AS_NUMBER function in a query to cast a VARIANT value to a NUMBER value:

SELECT AS_NUMBER(number1, 6, 3) number_value
  FROM as_number_example;

Copy

+--------------+
| NUMBER_VALUE |
|--------------|
|        2.340 |
+--------------+