Convert decimal to euro currency in MYSQL

Question

What is the best way to select a decimal value from the mysql database and convert it to an euro currrency in the mysql selection.

When i select "format(multiplier * (coalesce(Invoice.rent, 0) + coalesce(Invoice.furnished, 0) + coalesce(Invoice.gwe, 0) + coalesce(Invoice.service, 0) + coalesce(Invoice.gas, 0) + coalesce(Invoice.electricity, 0) + coalesce(Invoice.water, 0) + coalesce(Invoice.heat, 0) + coalesce(Invoice.tax, 0) + coalesce(Invoice.internet_tv, 0)) - coalesce(sum(Payment.amount),0),2)"

It returns a number like this 1,000.25 i want it to be 1.000,25 what is the best way to do this in MYSQL select query?

@Jordan Why should i do formatting in the application? Isn't it better to get the data as ready as possible delivered to your app? — Jelle Keizer, Dec 18 '14 at 17:19
Where does that line of reasoning end? If you're building a web app, should you have MySQL wrap values in HTML tags so your application doesn't have to? Formatting is a presentation operation. Let your database do data operations and your application worry about presentation. — Jordan Running, Dec 18 '14 at 17:26
Well a euro currency is a pretty common format its not some fancy made up format so i assumed that mysql at least had a way to format this. But it doesnt seem so, so i will do it in the app. — Jelle Keizer, Dec 18 '14 at 17:31
@JelleKeizer Do it in your application. Customizing your queries for locales is counter-productive and has the effect of converting numbers to strings for no reason. Many applications have a per-user locale where they can set their desired formatting, something impractical to push all the way down to the database level. The goal is to get the data in the most usable native format, not in the most "ready" format. — tadman, Dec 18 '14 at 17:32
Formatting should really be the last thing you do before displaying the value to the user. For example, what if later on you decide you want to do an additional calculation, like adding a fee on to the total based on user input? If your application only has a formatted string instead of a number, you've created a problem for yourself—now you have to go back and change your query and then put the formatting logic in your application. You'll save yourself a lot of trouble by just doing that to begin with. — Jordan Running, Dec 18 '14 at 17:38

Álvaro González · Accepted Answer · 2022-09-05T08:50:11.307

The documentation reveals that FORMAT() has up to three arguments (emphasis mine):

FORMAT(X,D[,locale])

Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part.

The optional third parameter enables a locale to be specified to be used for the result number's decimal point, thousands separator, and grouping between separators. Permissible locale values are the same as the legal values for the lc_time_names system variable (see Section 10.7, “MySQL Server Locale Support”). If no locale is specified, the default is 'en_US'.

That could be a starting point. (You apparently want the format provided by the de_DE locale.)

Demo

score 0 · Answer 2 · edited May 23 '17 at 11:45

In database you cant change default decimal separator from . to , for reason described here insert-non-english-decimal-points-in-mysql.

You can use this function to format your output value:


    CREATE FUNCTION f_change_decimal_separator(num_value VARCHAR(16)) RETURNS VARCHAR(16)
    BEGIN
      DECLARE char_value VARCHAR(16);
      SET char_value = REPLACE(num_value, ".", ";");
      SET char_value = REPLACE(char_value, ",", ".");
      SET char_value = REPLACE(char_value, ";", ",");
      RETURN char_value;
    END;

Convert decimal to euro currency in MYSQL

2 Answers2