Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Format the Amount Expand / Collapse
Author
Message
Posted Saturday, May 18, 2013 12:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 11, 2014 3:17 AM
Points: 206, Visits: 512
Dear,
My client requires to add a hyphen(-) in lieu of comma(,) in the field of amount. But client requires only one hyphen(-) in the amount. For example, Total price is 10,000,000 but new requirement is 10-000000.

Say if I execute "select amount from mytable". The result is '10-000000'. Here I need to do some formatting. But I have no idea.

Please help me to sort out the problem.

Rgds,
Akbar
Post #1454237
Posted Saturday, May 18, 2013 2:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:42 AM
Points: 1,943, Visits: 20,180
normally formatting is better left to the front end app to perform.
sometimes however this isn't always poss or desired.....

so in this case, how does your client want the following displayed?

-10,000,000
1
10
100
1,000
10,000
100,000
1,000,000


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1454242
Posted Saturday, May 18, 2013 7:28 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:10 PM
Points: 3,115, Visits: 3,239
There is always solution for a particular case. But what about cases without any commas?
Here is one proposal:

declare @amt varchar(20)
set @amt = '10,000,000.00'
--desired is 10-000000.00
set @amt = substring(replace(@amt,',','-'),1,charindex('-',replace(@amt,',','-'),1))+substring(replace(@amt,',',''),charindex('-',replace(@amt,',','-'),1),len(replace(@amt,',',''))-(charindex(replace(@amt,',','-'),'-',1)))
print @amt

IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1454255
Posted Saturday, May 18, 2013 6:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:11 PM
Points: 35,772, Visits: 32,441
shohelr2003 (5/18/2013)
Dear,
My client requires to add a hyphen(-) in lieu of comma(,) in the field of amount. But client requires only one hyphen(-) in the amount. For example, Total price is 10,000,000 but new requirement is 10-000000.

Say if I execute "select amount from mytable". The result is '10-000000'. Here I need to do some formatting. But I have no idea.

Please help me to sort out the problem.

Rgds,
Akbar


No reflection on you, Akbar.

That's one of the oddest formatting requirements I've ever seen for numeric values. Why on Earth does the client need it that way?



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1454293
Posted Monday, May 20, 2013 3:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 11, 2014 3:17 AM
Points: 206, Visits: 512
Thanks, IgorMi. It works.

@Jeff Moden, this is earth, the most interesting place.
Post #1454454
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse