MySQL format number output, thousands separator

Question

I have a mySQL query that outputs decimal fields with a semicolon.

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 

GBPam and GBPpm are decimal(10,5) types decimal(10,5)

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

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

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 

You see that the 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 then have to pounce on PHP to remove the comma. I would rather just force mysql to format it correctly.

Answer 1

Just use ROUND , this is a numerical function. FORMAT - string function

 ROUND(Fixes.GBPam, 3) 

Answer 2

you can use replace for this purpose.

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

EDIT: As for your question, you can 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 

Answer 3

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

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