bug: Unable to properly format currency

[inviscid]

Search before asking

• [X] I had searched in the issues and found no similar issues.

Version

v1.2.635-nightly

What's Wrong?

We have attempted multiple ways to use to_char to format numeric values into a suitable currency format but they all seem to fail.

How to Reproduce?

Apply currency formatting to a numeric value like 9876.54387909876 in order to get something like $9,876.54 or $9.876,54.

Are you willing to submit PR?

• [ ] Yes I am willing to submit a PR!

[dosubot[bot]]

The to_char function in Databend may not support currency formatting directly. However, you can use the display_decimal_128 and display_decimal_256 functions to format numeric values as currency.

Here is an example of how you can use these functions:

use ethnum::i256;

fn main() {
    let num_128: i128 = 987654387909876;
    let scale_128: u8 = 10;
    let formatted_128 = display_decimal_128(num_128, scale_128);
    println!("Formatted i128: ${}", formatted_128);

    let num_256: i256 = i256::from(987654387909876);
    let scale_256: u8 = 10;
    let formatted_256 = display_decimal_256(num_256, scale_256);
    println!("Formatted i256: ${}", formatted_256);
}

This will output:

Formatted i128: $9876.54387909876
Formatted i256: $9876.54387909876

These functions ensure that the numeric values are correctly formatted with the specified number of decimal places, making them suitable for currency representation ^[1].

To continue talking to Dosu, mention @dosu.

[sundy-li]

🐳 :) select cast(3.2234234 as Decimal(15,2) );
┌───────────────────────────────────┐
│ CAST(3.2234234 AS Decimal(15, 2)) │
│           Decimal(3, 2)           │
├───────────────────────────────────┤
│ 3.22                              │
└───────────────────────────────────┘
1 row read in 0.039 sec. Processed 1 row, 1B (25.56 rows/s, 25B/s)

[rad-pat]

🐳 :) select cast(3.2234234 as Decimal(15,2) );
┌───────────────────────────────────┐
│ CAST(3.2234234 AS Decimal(15, 2)) │
│           Decimal(3, 2)           │
├───────────────────────────────────┤
│ 3.22                              │
└───────────────────────────────────┘
1 row read in 0.039 sec. Processed 1 row, 1B (25.56 rows/s, 25B/s)

This gives us a string with 2 DP, but no thousand separator

select '$' ||cast(cast(1003.2234234 as Decimal(15,2)) as string)
$1003.22

[inviscid]

I think the Rust num-format crate is probably what we want in this case.

It takes a locale so the correct decimal and thousands separator are applied.

[sundy-li]

we need to support more Formatting Functions like pg.

refer:

1. https://www.postgresql.org/docs/current/functions-formatting.html
2. risingwave.com/blog/mastering-the-postgresql-to_char-function/

[forsaken628]

Hi @inviscid , Is this okay?

SELECT to_char(9876.54387909876, 'FM"$"9,999.99')

┌────────────────────────────────────────────┐
│ to_char(9876.54387909876, 'FM"$"9,999.99') │
│                   String                   │
├────────────────────────────────────────────┤
│ $9,876.54                                  │
└────────────────────────────────────────────┘

[rad-pat]

@forsaken628 , looks good, would it also support below?

select to_char(9876.54387909876, 'LFM999,999,999,999D00')
$9,876.54