Menu
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
494
MySQL FORMAT() FunctionThe FORMAT() function formats a number to a format like "#,###,###. ##", rounded to a specified number of decimal places, then it returns the result as a string.
Special characters such as commas and quotes must be "escaped," which means you place a backslash in front of the character to insert the character as a part of the MySQL string.
MySQL ROUND() Function The ROUND() function rounds a number to a specified number of decimal places.
We can use the FORMAT() function in MySQL to format numbers into a more human readable format. The function returns the number as a formatted string. It adds thousands separators and a decimal separator as required, and rounds the results to the given decimal position.
This post seems to have a much cleaner solution:
Just use
ROUND, this is a numeric function.FORMATis a string function
103
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
