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.
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;
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
SSC-EWI-OR0050: Input Expression is out of the range.
SSC-EWI-OR0045: Cast type L and FML is not supported.
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]
Last updated