[SOLVED] MySQL - Thousands separator

MySQL - Thousands separator

I would like to know if there is a way to get a thousand separator in an SQL Query ?

The request looks like this :

select coalesce(user, 'TOTAL') as "user",
    concat(count(items), ' items') as "nb_items",
    concat(replace(sum(amount), '.', ','), ' €') as "total_in_euro",
    concat(
        round((sum(amount) / (select sum(amount) from my_db) * 100), 2),
        ' %'
    ) as "share"
from my_db
group by user
with rollup

Currently, the CSV result is:

user;nb_items;total_in_euro
usr_1;20 items;20000 €;10,01 %
usr_2;254 items;17852,12 €;8,12 %

What I want is a result like that :

user;nb_items;total_in_euro
usr_1;20 items;20 000 €;10,01 %
usr_2;254 items;17 852,12 €;8,12 %

Is there a way to do so ?

Solution

I don't know how to do it with a space, but the standard (with a point or a comma [germany i.e.]) seperation can be achieved with FORMAT().

Have a look at the Format Function

mysql> SELECT FORMAT(12332.123456, 4);
    -> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
    -> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
    -> '12,332'
mysql> SELECT FORMAT(12332.2,2,'de_DE');
    -> '12.332,20'
mysql> SELECT FORMAT(12332.2,2,'pt_BR');
    -> '12332,20'