asked on

Output number as text with four decimal places and commas in SQL Server 2008

Hi!

I'm working on a view where I need to output a string containing numeric data.

The data is stored as a 'real' datatype, so it looks like

1123.38791
or a different number in the same data may look like

73.23

I need them both formatted the same for output . . .

1,123.3879 mL
73.2300 mL

in other words, I need four significant decimal places on the right and comma in the thousands position on the left. Formatting as Money doesn't work because it won't let you combine four decimal places with commas on the left as far as I can tell.

Any suggestions? All I can think to do is convert each number twice and combine the left side with the commas from one conversion with the decimal point and the four digits to the right.

Isn't there a way to do this without writing some horribly complex function? Any suggestions?

Thanks!
Karen

ASKER CERTIFIED SOLUTION

rjbook

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

klgrube

ASKER

I did say this was part of a string I needed to output, so telling me to do it on the client or in a application wasn't an appropriate answer. And I sure could have used some direction regarding some kind of function that would do the formatting I need.

klgrube

ASKER

I apologize if that seemed a bit harsh. I take so much time and effort in making my questions clear if not concise, that it's a little upsetting to get an answer back that couldn't possibly give me the solution I needed given the actual question I asked. But I recognize the effort and should have allowed further input. Part of the problem is that things happen very quickly in the work I'm doing, and if I can't fix a problem in a day or so, I just have to move on. And it often takes days of going back and forth to get a complete or truly helpful answer. I don't mean to be impatient, but a couple of days is normally more than I have to fix a problem when I have a deadline and other people are depending on me, and in this case, I just didn't have time to go back and forth.