?
Solved

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

Posted on 2009-04-28
4
Medium Priority
?
1,034 Views
Last Modified: 2012-08-14
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

0
Comment
Question by:klgrube
  • 2
3 Comments
 
LVL 3

Accepted Solution

by:
rjbook earned 1500 total points
ID: 24257098
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.


0
 

Author Closing Comment

by:klgrube
ID: 31575785
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.
0
 

Author Comment

by:klgrube
ID: 24288689
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

749 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