Snowflake 2 decimal places

Question

I have a data column that is a number and it has nulls, dashes, and commas. I want to remove all and replace with 0.00. Also I have some numbers that are 11.4 instead of 11.40. How do I fix this? also something either nvl or round adds the comma back in.

I tried round, I tried doing ;;decimal(20,2), I tried a replace to 0.00. Nothing seems to work.

round(nvl(replace(replace(aum_total,'-',0),',',''),0)) as aum_total

can you show a table of "input" and wanted "output" because it help to cross reference with the words you have used. — Simeon Pilgrim, Nov 16 '22 at 00:54

Simeon Pilgrim · Accepted Answer · 2022-11-16T03:51:38.773

so you are converting dash to zero makes low sense, from a guessing intent.

and the coma point I assume to to remove thousands, so to add that back in needs to be done via TO_CHAR via formatting.

And the trailing zeros also is a formatting thing..

select
    column1 as aum_total
    ,translate(aum_total, '-', '0') as t1
    ,replace(t1, ',', '') as r1
    ,zeroifnull(r1) as z1
    ,to_number(z1, 20, 2) as n1
    ,to_char(n1, '9,999,999.00') as result
from values 
    (null),
    ('1-7'),
    ('1,8'),
    ('11.4'),
    ('11.40'),
    ('11.400'),
    ('11.1234'),
    ('1,234.567');

gives:

AUM_TOTAL	T1	R1	Z1	N1	RESULT
null	null	null	0	0	0.00
1-7	107	107	107	107	107.00
1,8	1,8	18	18	18	18.00
11.4	11.4	11.4	11.4	11.4	11.40
11.4	11.4	11.4	11.4	11.4	11.40
11.4	11.4	11.4	11.4	11.4	11.40
11.1234	11.1234	11.1234	11.1234	11.12	11.12
1,234.567	1,234.567	1234.567	1,234.567	1,234.57	1,234.57

Thinking about this more, I now assume you mean you want to replace a string that is only a dash, with zero, as that is standard accounting, and not all dashes, thus not mess-up negative numbers, so swapping to regexp_replace, and the number cast can be skip it seems also!

select
    column1 as aum_total
    ,trim(aum_total) as t1
    ,regexp_replace(t1, '^-$', '0') as r1
    ,replace(r1, ',', '') as r2
    ,zeroifnull(r2) as z1
    ,to_char(z1, '9,999,999.00') as result
from values 
    (null),
    (' - '),
    (' -10.123 '),
    ('-'),
    ('-10.123'),
    ('1,8'),
    ('11.4'),
    ('11.40'),
    ('11.400'),
    ('11.1234'),
    ('1,234.567');

Snowflake 2 decimal places

1 Answers1