Dom Sinclair Dom Sinclair - 2 years ago 73
SQL Question

What is the best way to reformat this piece of SQL to avoid the thousands separator

As part of a SQL stored procedure (producing XML output) I have the following line:

FORMAT(Quantity * ld.UnitPrice, 'N2') AS '@value'

This has produced perfectly acceptable results for several months now. Last week however the service into which the XML that this procedure produces failed because the value was over a thousand pounds (sterling), producing the following output

It would have been nice had the service documentation mentioned the fact that it didn't like commas in the values passed back to it as attributes but that argument is for another day.

I'm after some advice as to the best way to reformat this one line so that it does not produce anything that separates thousands, in other words I need it to produce the following
or something similar whenever a value over one thousand pounds is arrived at. It still needs to retain the two decimal places.

Although I have mentioned specifically Pounds sterling in truth this format needs to be culture neutral, so that all figures that rise above a thousand do not have a separator.

Answer Source

You could probably remove the format function. Then if required add convert(decimal(9,2), YourColumn)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download