TO_NUMBER

Description

Converts an input expression to a fixed-point number. For NULL input, the output is NULL.

Arguments

Required:

<expr>

An expression of a numeric, character, or variant type.

Optional:

<format>

The SQL format model used to parse the input expr and return. For more information, see SQL Format Models.

<precision>

The maximal number of decimal digits in the resulting number; from 1 to 38. In Snowflake, precision is not used for determination of the number of bytes needed to store the number and does not have any effect on efficiency, so the default is the maximum (38).

<scale>

The number of fractional decimal digits (from 0 to precision - 1). 0 indicates no fractional digits (i.e. an integer number). The default scale is 0.

Returns

The function returns NUMBER(precision, scale).

  • If the precision is not specified, then it defaults to 38.

  • If the scale is not specified, then it defaults to 0.

To more information check the TO_NUMBER in snowflake documentation.

IN -> Oracle_01.sql
SELECT CAST('123,456E+40' AS NUMBER, '999,999EEE') FROM DUAL;
SELECT CAST('12sdsd3,456E+40' AS NUMBER, '999,999EEE') FROM DUAL;
SELECT CAST('12345sdsd' AS NUMBER, '99999') FROM DUAL;
SELECT CAST('12.345678912345678912345678912345678912' AS NUMBER, '99.999999999999999999999999999999999999') FROM DUAL;
SELECT CAST('               12.345678912345678912345678912345678912' AS NUMBER, '99.999999999999999999999999999999999999') FROM DUAL;
SELECT CAST('               -12.345678912345678912345678912345678912' AS NUMBER, '99.999999999999999999999999999999999999') FROM DUAL;
SELECT CAST('12.34567891234567891234567891234567891267' AS NUMBER, '99.999999999999999999999999999999999999') FROM DUAL;
SELECT CAST('123.456E-40' AS NUMBER, '999.9999EEE') FROM DUAL;
select cast('12,345,678,912,345,678,912,345,678,912,345,678,912' as number, '99,999,999,999,999,999,999,999,999,999,999,999,999') from dual;
SELECT CAST('  123.456E-40' AS NUMBER, '999.9999EEE') FROM DUAL;
select cast('       12,345,678,912,345,678,912,345,678,912,345.678912' as number, '99,999,999,999,999,999,999,999,999,999,999.999999') from dual;

SELECT CAST('12.34567891234567891234567891234567891267+' AS NUMBER, '99.999999999999999999999999999999999999S') FROM DUAL;
select cast('12,345,678,912,345,678,912,345,678,912,345,678,912+' as number, '99,999,999,999,999,999,999,999,999,999,999,999,999S') from dual;

select cast('12.48+' as number, '99.99S') from dual;
select cast('  12.48+' as number, '99.99S') from dual;
select cast('12.48+   ' as number, '99.99S') from dual;

SELECT CAST('123.456+E-2' AS NUMBER, '999.9999SEEE') FROM DUAL;
SELECT CAST('123.456+E-2-' AS NUMBER, '999.9999SEEE') FROM DUAL;

SELECT CAST('12356-' AS NUMBER, '99999S') FROM DUAL;

select cast(' 1.0E+123' as number, '9.9EEEE') from dual;
select cast('1.2E+02' as number, 'FM9.9EEEE') from dual;
select cast('123.45' as number, 'FM999.009') from dual;
select cast('123.00' as number, 'FM999.009') from dual;
select cast(' $123.45' as number, 'L999.99') from dual;
select cast('$123.45' as number, 'FML999.99') from dual;
select cast('1234567890+' as number, '9999999999S') from dual;

OUT -> Oracle_01.sql
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0050 - INPUT EXPRESSION IS OUT OF THE RANGE '123,456E+40' ***/!!!
 CAST('123,456E+40' AS NUMBER(38, 18) , '999,999EEE') FROM DUAL;

SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0053 - INCORRECT INPUT FORMAT '12sdsd3,456E+40' ***/!!! CAST('12sdsd3,456E+40' AS NUMBER(38, 18) , '999,999EEE') FROM DUAL;

SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0053 - INCORRECT INPUT FORMAT '12345sdsd' ***/!!! CAST('12345sdsd' AS NUMBER(38, 18) , '99999') FROM DUAL;

SELECT
 TO_NUMBER('12.345678912345678912345678912345678912', '99.999999999999999999999999999999999999', 38, 36)
FROM DUAL;

SELECT
 TO_NUMBER('               12.345678912345678912345678912345678912', '99.999999999999999999999999999999999999', 38, 36)
FROM DUAL;

SELECT
 TO_NUMBER('               -12.345678912345678912345678912345678912', '99.999999999999999999999999999999999999', 38, 36)
FROM DUAL;

SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0050 - INPUT EXPRESSION IS OUT OF THE RANGE '12.34567891234567891234567891234567891267' ***/!!! CAST('12.34567891234567891234567891234567891267' AS NUMBER(38, 18) , '99.999999999999999999999999999999999999') FROM DUAL;

SELECT
 TO_NUMBER('123.456E-40', '999.9999EEE', 38, 37)
FROM DUAL;

select
 TO_NUMBER('12,345,678,912,345,678,912,345,678,912,345,678,912', '99,999,999,999,999,999,999,999,999,999,999,999,999', 38, 0)
from dual;

SELECT
 TO_NUMBER('  123.456E-40', '999.9999EEE', 38, 37)
FROM DUAL;

select
 TO_NUMBER('       12,345,678,912,345,678,912,345,678,912,345.678912', '99,999,999,999,999,999,999,999,999,999,999.999999', 38, 6)
from dual;

SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0050 - INPUT EXPRESSION IS OUT OF THE RANGE '12.34567891234567891234567891234567891267+' ***/!!! CAST('12.34567891234567891234567891234567891267+' AS NUMBER(38, 18) , '99.999999999999999999999999999999999999S') FROM DUAL;

select
 TO_NUMBER('12,345,678,912,345,678,912,345,678,912,345,678,912+', '99,999,999,999,999,999,999,999,999,999,999,999,999S', 38, 0)
from dual;

select
 TO_NUMBER('12.48+', '99.99S', 38, 2)
from dual;

select
 TO_NUMBER('  12.48+', '99.99S', 38, 2)
from dual;

select
 TO_NUMBER('12.48+   ', '99.99S', 38, 2)
from dual;

SELECT
 TO_NUMBER('123.456+E-2', '999.9999SEEE', 38, 5)
FROM DUAL;

SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0053 - INCORRECT INPUT FORMAT '123.456+E-2-' ***/!!! CAST('123.456+E-2-' AS NUMBER(38, 18) , '999.9999SEEE') FROM DUAL;

SELECT
 TO_NUMBER('12356-', '99999S', 38, 0)
FROM DUAL;

select
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0050 - INPUT EXPRESSION IS OUT OF THE RANGE ' 1.0E+123' ***/!!! cast(' 1.0E+123' as NUMBER(38, 18) , '9.9EEEE') from dual;

select
 TO_NUMBER('1.2E+02', 'FM9.9EEEE', 38, 0)
from dual;

select
 TO_NUMBER('123.45', 'FM999.009', 38, 2)
from dual;

select
 TO_NUMBER('123.00', 'FM999.009', 38, 2)
from dual;

select
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0045 - CAST TYPE L AND FML NOT SUPPORTED ***/!!! cast(' $123.45' as NUMBER(38, 18) , 'L999.99') from dual;

select
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0045 - CAST TYPE L AND FML NOT SUPPORTED ***/!!! cast('$123.45' as NUMBER(38, 18) , 'FML999.99') from dual;

select
 TO_NUMBER('1234567890+', '9999999999S', 38, 0)
from dual;

Related EWIs

  1. SSC-EWI-OR0050: Input Expression is out of the range.

  2. SSC-EWI-OR0045: Cast type L and FML is not supported.

  3. SSC-EWI-OR0053: Incorrect input format.

Recommendations

  • No additional user actions are required.

  • If you need more support, you can email us at [email protected]

PreviousTRUNC (number) UDFNextNLSSORT

Last updated