Solved

how to convert decimal value in to comma seperated value.

Posted on 2004-10-07
7
609 Views
Last Modified: 2009-07-29
How to write select statment for DECIMAL(28,2) field with "," every thousands place.

For Example

Select QTY from mytable
QTY
----------------------
256110054720.21

Output should be
QTY
-----
256,110,054,720.21

0
Comment
Question by:Bharat Guru
7 Comments
 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
Presentation issues should be handled on the front-end side.
If you change amounts into strings, they wont sort properly, they won't sum any more , ...
0
 

Author Comment

by:Bharat Guru
Comment Utility
you are right byt how can i convert in select statment.
somethinkg like this....
select convert(qty,...) from my table
0
 
LVL 17

Expert Comment

by:BillAn1
Comment Utility
if you are looking at the result in Query Analyser, you can have it formatted according to your local settings by going to tools->options->connections and select the "use regional settings" option. Then, make sure your reginal settings (from Control Panel) have comma seperator etc.
If you are passing the results on to an application, you are much better off have the application do the formatting, as otherwise you would have to change the data type from decimal to varchar()  etc.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
There's no built-in support for this format
instead, most programming languages have formatNumber or formatCurrency functions,
and most reporting tools including excel have standard supprto for htis kind of display format

This could be achieved with a user-defined funciton that would
- cast the number as a string
- find decimal point, if any
- loop every 3 digits left
- build the string in the loop

The function will be ugly and performances will be affected badly

Do you really want this ?
0
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 20 total points
Comment Utility
I agree that it's better to do it in the front end, but you can do it in a SELECT statement. Use CONVERT with a style argument of 1, and the value being converted has to be a money datatype, so if the original value isn't money, you have to CAST it before the CONVERT function does it's thing:

DECLARE @d as decimal(20,2)
SET @d = 256110054720.21
SELECT Convert(varchar(50), Cast(@d as money) ,1)
0
 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
>>The function will be ugly and performances will be affected badly<<
Forgive me for this mis-appreciation

jdlambert has a cool way to get the output you need, and I've learnt something new today ;-)
0
 
LVL 15

Expert Comment

by:jdlambert1
Comment Utility
My memory's so bad, everything I learn is new...  :)
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

763 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

12 Experts available now in Live!

Get 1:1 Help Now