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;
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;
