Numeric Format Models

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

multiple_nines	exact_zeros	decimals	fill_mode	currency
9423	9423	123.456	123031.30	1254.88

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

multiple_nines	exact_zeros	decimals	fill_mode	currency
9423	9423	123.456	123031.300	12454.88

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

currency_L	signed_number_end	plus_sign	minus_sign	signed_number
12454.8	-123	1254.88	-12454.88	-12454.88

Output:

Snowflake

OUT -> Redshift_02.sql

select to_number('$ 12,454.88', 'FML99G999D99') !!!RESOLVE EWI!!! /*** SSC-EWI-PG0005 - '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-PG0005 - '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

currency_L	signed_number_end	plus_sign	minus_sign	signed_number
12454.8	-123	1254.88	-12454.88	-12454.88

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

signed_number	decimal_number	negative	minus_sign	plus_sign	multiple_ninesmultiple_nines	exact_zerosexact_zeros
'123-'	'12,454.88'	'-12,454.88'	'12454.88'	'-12,454.88'	'09423'	'09423'

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

signed_number	decimal_number	negative	minus_sign	plus_sign	multiple_ninesmultiple_nines	exact_zerosexact_zeros
'123-'	'12,454.88'	'-12,454.88'	'12454.88'	'-12,454.88'	'09423'	'09423'

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-PG0005 - 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-PG0005: The current date/numeric format may have a different behavior in Snowflake.