How do i format a number to be in the following format 31_235,3400 , I tried using to_char() built in function, but every time I am getting wrong output.
So the selling price of the drug should be displayed in a format with four decimals that will always be displayed and separated by a decimal point, while the integer will always be displayed and as a separator for thousands it will use an underscore, for example: 31_235,3400
Here is my tried query
SELECT
fk_id,
prod_id,
a_id,
CASE
WHEN LENGTH(TO_CHAR(price, 'FM999999999D9999999')) <= 4 THEN TO_CHAR(price, '999D0000')
ELSE TO_CHAR(price, 'FM999999999999999_999D0000') END as price
FROM sellings
Here is the input table
| FK_id | price |
|---|---|
| 1 | 156.00 |
| 1 | 213.00 |
| 1 | 359.00 |
| 1 | 620.00 |
| 1 | 110.00 |
| 1 | 97.00 |
| 1 | 1000.00 |
| 1 | 1090.40 |
The output should be as described above
| FK_id | price |
|---|---|
| 1 | 156.0000 |
| 1 | 213.0000 |
| 1 | 359.0000 |
| 1 | 620.0000 |
| 1 | 110.0000 |
| 1 | 97.0000 |
| 1 | 1_000.0000 |
| 1 | 1_090.4000 |
CodePudding user response：
set lc_numeric = 'de_DE.UTF-8';
select TO_CHAR(10000000.234, 'FM99G999G999G9999G999G999D0000');
to_char
-----------------
10.000.000,2340
select replace(TO_CHAR(10000000.234, 'FM99G999G999G9999G999G999D0000'), '.', '_');
replace
-----------------
10_000_000,2340
UPDATE for fr_FR.UTF-8 locale.
set lc_numeric = 'fr_FR.UTF-8';
select TO_CHAR(10000000.234, 'FM99G999G999G9999G999G999D0000');
to_char
-----------------
10 000 000,2340
select replace(TO_CHAR(10000000.234, 'FM99G999G999G9999G999G999D0000'), ' ', '_');
replace
-----------------
10_000_000,2340