Format currency amount using Indian numeral separator in MySQL
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?
java mysql
add a comment |
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?
java mysql
2
FORMAT()
should be your friend
– B001ᛦ
Nov 13 '18 at 11:59
2
You should do this in your program logic and not in SQL
– juergen d
Nov 13 '18 at 12:01
@juergend actually i am using java but don't know how to do there
– manish thakur
Nov 13 '18 at 12:03
add a comment |
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?
java mysql
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?
java mysql
java mysql
edited Feb 2 at 20:33


halfer
14.5k758112
14.5k758112
asked Nov 13 '18 at 11:58


manish thakurmanish thakur
48513
48513
2
FORMAT()
should be your friend
– B001ᛦ
Nov 13 '18 at 11:59
2
You should do this in your program logic and not in SQL
– juergen d
Nov 13 '18 at 12:01
@juergend actually i am using java but don't know how to do there
– manish thakur
Nov 13 '18 at 12:03
add a comment |
2
FORMAT()
should be your friend
– B001ᛦ
Nov 13 '18 at 11:59
2
You should do this in your program logic and not in SQL
– juergen d
Nov 13 '18 at 12:01
@juergend actually i am using java but don't know how to do there
– manish thakur
Nov 13 '18 at 12:03
2
2
FORMAT()
should be your friend– B001ᛦ
Nov 13 '18 at 11:59
FORMAT()
should be your friend– B001ᛦ
Nov 13 '18 at 11:59
2
2
You should do this in your program logic and not in SQL
– juergen d
Nov 13 '18 at 12:01
You should do this in your program logic and not in SQL
– juergen d
Nov 13 '18 at 12:01
@juergend actually i am using java but don't know how to do there
– manish thakur
Nov 13 '18 at 12:03
@juergend actually i am using java but don't know how to do there
– manish thakur
Nov 13 '18 at 12:03
add a comment |
2 Answers
2
active
oldest
votes
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.
1
thanx it worked..
– manish thakur
Nov 13 '18 at 12:15
hey it is giving some wrong results like for 27227004 it shows 27,227,004 but actually it should show 2,72,27,004.. so any suggestion please
– manish thakur
Nov 19 '18 at 5:18
@manishthakur maybe you don't have locale support installed/configured? Using the DB Fiddle provided in the answer with "27227004" it correctly returns2,72,27,004.00
– Jari Keinänen
Nov 19 '18 at 11:08
@JariKeinänen yupp i am using mysql5 and this format function is not there ..can you helpme out that how colud i achieve that using java?
– manish thakur
Nov 19 '18 at 11:11
@manishthakur what is your exact MySQL server version ?
– Madhur Bhaiya
Nov 20 '18 at 2:09
|
show 10 more comments
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.)
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53280570%2fformat-currency-amount-using-indian-numeral-separator-in-mysql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
1
thanx it worked..
– manish thakur
Nov 13 '18 at 12:15
hey it is giving some wrong results like for 27227004 it shows 27,227,004 but actually it should show 2,72,27,004.. so any suggestion please
– manish thakur
Nov 19 '18 at 5:18
@manishthakur maybe you don't have locale support installed/configured? Using the DB Fiddle provided in the answer with "27227004" it correctly returns2,72,27,004.00
– Jari Keinänen
Nov 19 '18 at 11:08
@JariKeinänen yupp i am using mysql5 and this format function is not there ..can you helpme out that how colud i achieve that using java?
– manish thakur
Nov 19 '18 at 11:11
@manishthakur what is your exact MySQL server version ?
– Madhur Bhaiya
Nov 20 '18 at 2:09
|
show 10 more comments
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.
1
thanx it worked..
– manish thakur
Nov 13 '18 at 12:15
hey it is giving some wrong results like for 27227004 it shows 27,227,004 but actually it should show 2,72,27,004.. so any suggestion please
– manish thakur
Nov 19 '18 at 5:18
@manishthakur maybe you don't have locale support installed/configured? Using the DB Fiddle provided in the answer with "27227004" it correctly returns2,72,27,004.00
– Jari Keinänen
Nov 19 '18 at 11:08
@JariKeinänen yupp i am using mysql5 and this format function is not there ..can you helpme out that how colud i achieve that using java?
– manish thakur
Nov 19 '18 at 11:11
@manishthakur what is your exact MySQL server version ?
– Madhur Bhaiya
Nov 20 '18 at 2:09
|
show 10 more comments
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.
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.
edited Nov 20 '18 at 10:25
answered Nov 13 '18 at 12:10


Madhur BhaiyaMadhur Bhaiya
19.6k62236
19.6k62236
1
thanx it worked..
– manish thakur
Nov 13 '18 at 12:15
hey it is giving some wrong results like for 27227004 it shows 27,227,004 but actually it should show 2,72,27,004.. so any suggestion please
– manish thakur
Nov 19 '18 at 5:18
@manishthakur maybe you don't have locale support installed/configured? Using the DB Fiddle provided in the answer with "27227004" it correctly returns2,72,27,004.00
– Jari Keinänen
Nov 19 '18 at 11:08
@JariKeinänen yupp i am using mysql5 and this format function is not there ..can you helpme out that how colud i achieve that using java?
– manish thakur
Nov 19 '18 at 11:11
@manishthakur what is your exact MySQL server version ?
– Madhur Bhaiya
Nov 20 '18 at 2:09
|
show 10 more comments
1
thanx it worked..
– manish thakur
Nov 13 '18 at 12:15
hey it is giving some wrong results like for 27227004 it shows 27,227,004 but actually it should show 2,72,27,004.. so any suggestion please
– manish thakur
Nov 19 '18 at 5:18
@manishthakur maybe you don't have locale support installed/configured? Using the DB Fiddle provided in the answer with "27227004" it correctly returns2,72,27,004.00
– Jari Keinänen
Nov 19 '18 at 11:08
@JariKeinänen yupp i am using mysql5 and this format function is not there ..can you helpme out that how colud i achieve that using java?
– manish thakur
Nov 19 '18 at 11:11
@manishthakur what is your exact MySQL server version ?
– Madhur Bhaiya
Nov 20 '18 at 2:09
1
1
thanx it worked..
– manish thakur
Nov 13 '18 at 12:15
thanx it worked..
– manish thakur
Nov 13 '18 at 12:15
hey it is giving some wrong results like for 27227004 it shows 27,227,004 but actually it should show 2,72,27,004.. so any suggestion please
– manish thakur
Nov 19 '18 at 5:18
hey it is giving some wrong results like for 27227004 it shows 27,227,004 but actually it should show 2,72,27,004.. so any suggestion please
– manish thakur
Nov 19 '18 at 5:18
@manishthakur maybe you don't have locale support installed/configured? Using the DB Fiddle provided in the answer with "27227004" it correctly returns
2,72,27,004.00
– Jari Keinänen
Nov 19 '18 at 11:08
@manishthakur maybe you don't have locale support installed/configured? Using the DB Fiddle provided in the answer with "27227004" it correctly returns
2,72,27,004.00
– Jari Keinänen
Nov 19 '18 at 11:08
@JariKeinänen yupp i am using mysql5 and this format function is not there ..can you helpme out that how colud i achieve that using java?
– manish thakur
Nov 19 '18 at 11:11
@JariKeinänen yupp i am using mysql5 and this format function is not there ..can you helpme out that how colud i achieve that using java?
– manish thakur
Nov 19 '18 at 11:11
@manishthakur what is your exact MySQL server version ?
– Madhur Bhaiya
Nov 20 '18 at 2:09
@manishthakur what is your exact MySQL server version ?
– Madhur Bhaiya
Nov 20 '18 at 2:09
|
show 10 more comments
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.)
add a comment |
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.)
add a comment |
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.)
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.)
edited Feb 2 at 20:34


halfer
14.5k758112
14.5k758112
answered Nov 20 '18 at 2:29
Rick JamesRick James
68.5k559100
68.5k559100
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53280570%2fformat-currency-amount-using-indian-numeral-separator-in-mysql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
FORMAT()
should be your friend– B001ᛦ
Nov 13 '18 at 11:59
2
You should do this in your program logic and not in SQL
– juergen d
Nov 13 '18 at 12:01
@juergend actually i am using java but don't know how to do there
– manish thakur
Nov 13 '18 at 12:03