mySQL format number output , thousands separator

Question

I have a mySQL query which is outputting decimal fields with a comma.

SELECT Metals.Metal, FORMAT(Fixes.GBPam, 3) AS AM, FORMAT(Fixes.GBPpm, 3) AS PM,     
DATE_FORMAT(Fixes.DateTime, '%d-%m-%y') AS Date
FROM Fixes, Metals
WHERE Metals.Id = Fixes.Metals_Id

Fields GBPam and GBPpm are both of type decimal(10,5)

Now I want columns AM and PM to be formatted to 3 decimal places in my sql query - Correct

I want values in the thousands to be formatted as xxxx.xxx and not x,xxx.xxx - Incorrect

Example output from mysql query:

Metal       AM          PM          Date
Gold        1,081.334   NULL    11-09-12
Silver      21.009      NULL    10-09-12
Platinum    995.650     NULL    11-09-12
Palladium   416.700     NULL    11-09-12

Can you see that output for Gold AM is 1,081.334? How can I get it to output 1081.334?

This is a pain in the ass for me because I have to then muck about in PHP to remove the comma. I would prefer to just get mysql to format it correctly.

score 12 · Accepted Answer · answered Sep 11 '12 at 11:10

12

Just use ROUND, this is a numeric function. FORMAT is a string function

ROUND(Fixes.GBPam, 3)

answered Sep 11 '12 at 11:10

Imre L

6,159
24
32

1

Round doesn't add decimal if ORIGINAL number does not have decimal – Abhishek Anand Nov 21 '19 at 13:01

heretolearn · Answer 2 · 2012-09-11T13:22:53.413

1

you can use replace command for this purpose.

 REPLACE(Fixes.GBPam,',','')

EDIT: With respect to your question you could do something like this:

SELECT Metals.Metal, ROUND(REPLACE(Fixes.GBPam,',',''),3) AS AM,
  ROUND(REPLACE(Fixes.GBPpm,',',''),3) AS PM,     
  DATE_FORMAT(Fixes.DateTime, '%d-%m-%y') AS Date
  FROM Fixes, Metals
WHERE Metals.Id = Fixes.Metals_Id

edited Sep 11 '12 at 13:22

answered Sep 11 '12 at 11:11

heretolearn

6,387
4
30
53

Does ROUND round up? e.g. what will round do to 1024.7854? Will I get back 1024.785 or 1024.76? – Gravy Sep 11 '12 at 12:29
it rounds up to the no. of decimal places specified. Check here https://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_round – heretolearn Sep 11 '12 at 13:24

score 1 · Answer 3 · edited Jun 25 '13 at 03:01

1

Use replace function. Whether the field is integer or varchar, it will work.

select replace(Fixes.GBPam,',','.');

edited Jun 25 '13 at 03:01

Community

1
1

answered Sep 11 '12 at 11:22

Sundar G

1,069
1
11
29

mySQL format number output , thousands separator

3 Answers3

Linked