Format the Amount

Question

Post reply

Format the Amount

shohelr2003

Hall of Fame

Points: 3535
More actions
May 18, 2013 at 12:02 am

#272825

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 2

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,My blog: www.igormicev.com

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 4

Thanks, IgorMi. It works.

@jeff Moden, this is earth, the most interesting place.