Format currency amount using Indian numeral separator in MySQL

Question

I have an query which is giving me total amount like 100000.0000 and I want to show this like 100000 has to be displayed as Rs. 1,00,000.00

Here is my query:

String sql = " select distinct round(sum(netamount)) as amount from syncbill where cancelled<>'Y' and year(curdate())=year(billdate)";
ResultSet resultSetYtd = statement.executeQuery(YtdQuery);
while (resultSetYtd.next()) {
  String AmountYtd = resultSetYtd.getString("amount");
  system.out.println(AmountYtd);

}

Can I achieve this with query only?

Answer 1

You are looking for Indian numeral separators (lakhs, crores). We can use Format() function, with third parameter set to en_IN (English - India) locale. Second parameter is set to 2 for specifying 2 decimal places after decimal point .. Finally, the CONCAT('Rs. ') adds the currency to the result.

select CONCAT('Rs. ', FORMAT(sum(netamount), 2, 'en_IN')) 
from syncbill

Demo

select format(100000,2,'en_IN');

| format(100000,2,'en_IN') |
| ------------------------ |
| 1,00,000.00              |

View on DB Fiddle

---

Edit: However, as discussed further in comments, your MySQL server version is significantly old (5.1) and it does not support Format() function with Locale parameter. Check 5.1 documentation here.

Answer 2

The changelog for 5.5.0 says

The FORMAT() function now supports an optional third parameter that 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 MySQL Server Locale Support). For example, the result from FORMAT(1234567.89,2,'de_DE') is 1.234.567,89. If no locale is specified, the default is 'en_US'.

So, it should be working if you have 5.5 or later. If you are still having troubles, provide further details.

(Caveat: Those links will break when 5.5 hits EOL. Edit the links to say 5.6; those may work.)