13

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

VLAZ
  • 26,331
  • 9
  • 49
  • 67
David Christy
  • 171
  • 2
  • 2
  • 4

4 Answers4

21

You could just:

REPLACE(FORMAT(contracts.charge, 2), ',', '')
Bohemian
  • 412,405
  • 93
  • 575
  • 722
16

This post seems to have a much cleaner solution:

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

Community
  • 1
  • 1
Reilly Sweetland
  • 199
  • 2
  • 4
  • As commented in the referenced answer, `round()` doesn’t add decimal digits. This doesn’t work as requested. – Manngo Jan 10 '22 at 08:08
4

A possible workaround would be replacing the comma after the format :

REPLACE(FORMAT(contracts.charge,2),',','') AS "contracts charge"
aleroot
  • 71,077
  • 30
  • 176
  • 213
-2

Invertir , to decimal . to miles REPLACE( REPLACE(REPLACE(FORMAT(contracts.charge,2),',','-') ,'.',',') ,'-','.') AS contracts_charge

Williams Nasser
  • 1
  • Really they should be setting their locale properly, not trying to do string replacement on the existing formatting like this. – Iguananaut Jan 30 '14 at 18:10