I'm looking something similar to using in the BigQuery:
NUMFORMAT(MyPrice, '$###.###,##')
Example: NUMFORMAT(99999999.99, '$###.###,##')
Result: 99.999.999,99
P.S. By not using JavaScripts and e.t.c.
Asked
Active
Viewed 658 times
1
Oksana Ok
- 515
- 3
- 7
- 19
-
This is NOT duped, WHY? As this current question involved ONLY BIG-QUERY – Oksana Ok Oct 21 '19 at 14:50
-
As far as I understand the only way is to do it through REGEX – Oksana Ok Oct 21 '19 at 14:52
-
i thought it is exact same as referenced question also have bigquery tag – Mikhail Berlyant Oct 21 '19 at 14:57
-
@MikhailBerlyant It was for bigquery but now I try to figure out how to do it by not using JavaScript and e.t.c.... I'm a bit shocked that Google does not have a native function for this, as it is kind of essential. – Oksana Ok Oct 21 '19 at 14:58
1 Answers
2
Below is in pure SQL (BigQuery Standard SQL)
#standardSQL
CREATE TEMP FUNCTION NUMFORMAT(number FLOAT64) AS (
REPLACE(REPLACE(REGEXP_REPLACE(FORMAT("%'.2f", number), r'([\d,]+).(\d+)', r'\1=\2'), ',', '.'), '=', ',')
);
SELECT
NUMFORMAT(99999.99),
NUMFORMAT(99999999.99),
NUMFORMAT(12345678.09),
NUMFORMAT(-999.99)
with output
Row f0_ f1_ f2_ f3_
1 99.999,99 99.999.999,99 12.345.678,09 -999,99
Mikhail Berlyant
- 165,386
- 8
- 154
- 230
-
-
-
With an updated version for positive value = `9.999.999.99` but it should be `9.999.999,99` – Oksana Ok Oct 21 '19 at 15:22
-
-
Found Solution - `REPLACE(REPLACE(REPLACE(FORMAT("%'.2f", -99999999.77),',','X'),'.',','),'X','.')` – Oksana Ok Oct 21 '19 at 15:32
-
Oh boy - I see now what you mean. that was typo just in typing output within my answer. Please double check and let me know. PS. from you previous comment - I though you expect to convert 9.999.999.99 to 9.999.999,99 - which obviously not supported in this answer at all – Mikhail Berlyant Oct 21 '19 at 15:44