Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

how to convert decimal value in to comma seperated value.

Posted on 2004-10-07
7
Medium Priority
?
661 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 12250026
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
ID: 12250071
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
ID: 12250102
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 26

Expert Comment

by:Hilaire
ID: 12250158
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 80 total points
ID: 12250304
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
ID: 12250501
>>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
ID: 12250520
My memory's so bad, everything I learn is new...  :)
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

610 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