Numeric Format Models

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

These are the different Numeric Formats supported by Redshift and its equivalent in Snowflake.

Redshift	Snowflake	Comments
0	0
9	9
. (period), D	. (period), D
, (comma)	, (comma)
CC		Currently there is no equivalent for Century Code in Snowflake.
FM	FM
PR		Currently there is no equivalent for this format in Snowflake.
S	S	Explicit numeric sign.
L	$	Currency symbol placeholder.
G	G
MI	MI	Minus sign (for negative numbers)
PL	S	Currently there is no equivalent for plus sign in Snowflake. So it is translated to the explicit numeric sign.
SG	S	Explicit numeric Sign in the specified position.
RN		Currently there is no equivalent for Roman Numerals in Snowflake.
TH		Currently there is no equivalent for Ordinal suffix in Snowflake

Sample Source Patterns

Uses in To_Number function

Input:

Redshift

IN -> Redshift_01.sql

select to_number('09423', '999999999') as multiple_nines
    , to_number('09423', '00000') as exact_zeros
    , to_number('123.456', '999D999') as decimals
    , to_number('123,031.30', 'FM999,999D999') as fill_mode
    , to_number('$ 12,454.88', '$999,999.99') as currency
;

Results

Output

Snowflake

OUT -> Redshift_01.sql

select to_number('09423', '999999999') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR ''999999999'' NODE ***/!!! as multiple_nines
    , to_number('09423', '00000') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR ''00000'' NODE ***/!!! as exact_zeros
    , to_number('123.456', '999D999') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR ''999D999'' NODE ***/!!! as decimals
    , to_number('123,031.30', 'FM999,999D999') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR ''FM999,999D999'' NODE ***/!!! as fill_mode
    , to_number('$ 12,454.88', '$999,999.99') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR ''$999,999.99'' NODE ***/!!! as currency
;

Results

Input:

Redshift

IN -> Redshift_02.sql

select to_number('$ 12,454.88', 'FML99G999D99') as currency_L
    , to_number('123-', '999S') as signed_number_end
    , to_number('+12454.88', 'PL99G999D99') as plus_sign
    , to_number('-12,454.88', 'MI99G999D99') as minus_sign
    , to_number('-12,454.88', 'SG99G999D99') as signed_number
;

Results

Output:

Snowflake

OUT -> Redshift_02.sql

select to_number('$ 12,454.88', 'FML99G999D99') !!!RESOLVE EWI!!! /*** SSC-EWI-0006 - 'FML99G999D99' FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!! as currency_L
    , to_number('123-', '999S') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR ''999S'' NODE ***/!!! as signed_number_end
    , to_number('+12454.88', 'PL99G999D99') !!!RESOLVE EWI!!! /*** SSC-EWI-0006 - 'PL99G999D99' FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!! as plus_sign
    , to_number('-12,454.88', 'MI99G999D99') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR ''MI99G999D99'' NODE ***/!!! as minus_sign
    , to_number('-12,454.88', 'SG99G999D99') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR ''SG99G999D99'' NODE ***/!!! as signed_number
;

Results

Uses in To_Char function

Input:

Redshift

IN -> Redshift_03.sql

select to_char(-123, '999S') as signed_number
    , to_char(12454.88, 'FM99G999D99') as decimal_number
    , to_char(-12454.88, '99G999D99') as negative
    , to_char(-12454.88, 'MI99G999D99') as minus_sign
    , to_char(+12454.88, 'PL99G999D99') as plus_sign
    , to_char(09423, '999999999') as multiple_nines
    , to_char(09423, '00000') as exact_zeros
;

Results

Output:

Snowflake

OUT -> Redshift_03.sql

select
    TO_CHAR(-123, '999S') as signed_number,
    TO_CHAR(12454.88, 'FM99G999D99') as decimal_number,
    TO_CHAR(-12454.88, '99G999D99') as negative,
    TO_CHAR(-12454.88, 'MI99G999D99') as minus_sign,
    TO_CHAR(+12454.88, 'S99G999D99') as plus_sign,
    TO_CHAR(09423, '999999999') as multiple_nines,
    TO_CHAR(09423, '00000') as exact_zeros
;

Results

Unsupported format

The following format is not supported, for which it will be marked with an EWI.

Input:

IN -> RedShift_04.sql

SELECT to_char(123031, 'th999,999')

Output:

OUT -> RedShift_04.sql

SELECT
TO_CHAR(123031, 'th999,999') !!!RESOLVE EWI!!! /*** SSC-EWI-0006 - th999,999 FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!!

Know Issues

1. Using numeric signs inside the number not supported.

When any numeric sign format (MI, SG or PL) is used inside the number, instead of at the start, or at the end of the number is not supported in snowflake

Example

select to_number('12,-454.88', '99GMI999D99')

Related EWIs

• SSC-EWI-0006: The current date/numeric format may have a different behavior in Snowflake.