MYSQL query - use format but not commas

Question

I am trying to use a format in an SQL query according to a report package. I have numbers that I need to show as a currency with two decimal places, so I use the format command to do this. The problem is that the format also puts a comma to separate thousands, and the report package sum function cannot handle this, so it treats the formatted numerical value as text and does not add it. The request uses: -

SELECT customers.name AS "customers name", FORMAT(contracts.charge,2) AS "contracts charge" FROM customers ORDER BY customers.name 

(the actual query is much more complicated, but this is the corresponding part)

To explain in more detail: -

If the numeric value is 123.1, then the formatted output is 123.10. If the numerical value is 1234.1, the formatted result is 1.234.10, which does not match

I need to find a way to convince a request for output 1234.10

Many thanks

Answer 1

You could simply:

 REPLACE(FORMAT(contracts.charge, 2), ',', '') 

Answer 2

There is a much cleaner solution in this post :

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

Answer 3

A possible workaround would be to replace the comma after the format:

 REPLACE(FORMAT(contracts.charge,2),',','') AS "contracts charge" 

Answer 4

Invert to decimal. miles REPLACE (REPLACE (REPLACE (FORMAT (contracts.charge, 2), ',', '-'), '.', ','), '-', '.') AS contract_charge