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


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

or a different number in the same data may look like


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?


Who is Participating?
Hello Karen,

Even with the new data types in SQL2008 I do not think there is anything "built in" that will do the formatting you are looking for in T-SQL. In fact formatting is usually handled at the Application Layer (or "Presentation" layer) and controlled by Localization on the client machine. Not all Countries format the way we do in the good old US of A so if possible I would think you are better off writing the T-SQL to get the data with a Cast to a Decimal (with the precision you need and a scale of 4 to get the padded zeros) as in this example;

cast(col1 as decimal(15,4))

This will not give you the commas you are looking for but SQL was never that great with string manipulation. As you say there is probably a way to get what you want from a custom function but overall I don't think you should t be doing that at the database level. Just my 2 cents.

klgrubeAuthor Commented:
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.
klgrubeAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.