Bharat Guru
asked on
how to convert decimal value in to comma seperated value.
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
For Example
Select QTY from mytable
QTY
----------------------
256110054720.21
Output should be
QTY
-----
256,110,054,720.21
ASKER
you are right byt how can i convert in select statment.
somethinkg like this....
select convert(qty,...) from my table
somethinkg like this....
select convert(qty,...) from my table
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->connection s 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.
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.
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 ?
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 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>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 ;-)
Forgive me for this mis-appreciation
jdlambert has a cool way to get the output you need, and I've learnt something new today ;-)
My memory's so bad, everything I learn is new... :)
If you change amounts into strings, they wont sort properly, they won't sum any more , ...