MYSQL Query - Use format but no commas

mysql format comma
David Christy picture David Christy · Dec 15, 2011 · Viewed 21.8k times · Source

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

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

(the actual query is a lot more complex but this is the relevant part)

To explain more fully:-

If the numeric value is 123.1 the formatted output is 123.10 which works. If the numeric value is 1234.1 the formatted output is 1,234.10 which does not

I need to find a way to persuade the query to output 1234.10

Many thanks

Answer

Bohemian picture Bohemian · Dec 15, 2011

You could just:

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

Related questions

Mysql: Setup the format of DATETIME to 'DD-MM-YYYY HH:MM:SS' when creating a table

After googling around, I cannot find a way to create a new table with a DATETIME column with the default format set to 'DD-MM-YYYY HH:MM:SS' I saw a tutorial in which it was done in phpmyadmin so I …

Read More
MySQL date formats - difficulty Inserting a date

I am trying to further a question I asked yesterday where I wanted to know how to query a date in a different format. But now I am trying to do an insert using this method (see below) however I …

Read More
SQL date format convert? [dd.mm.yy to YYYY-MM-DD]

is there mySQL function to convert a date from format dd.mm.yy to YYYY-MM-DD? for example, 03.09.13 -> 2013-09-03.

Read More