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

Posted on 2009-04-28
Last Modified: 2012-08-14

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?


Question by:klgrube
    LVL 3

    Accepted Solution

    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.


    Author Closing Comment

    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.

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now