Format varchar to currency.

Question

Format varchar to currency.

gtan018

SSChasing Mays

Points: 624
More actions
August 21, 2013 at 9:24 am

#281376

How to format varchar to currency. Negative amounts to ($100.50) with parentheses?

Viewing 6 posts - 1 through 6 (of 6 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Could you post DDL, sample data and expected results to give us a clearer idea of what you want?

Check the article linked on my signature to find out how. No need for lots of data or real data, just what should be necessary to give a good perspective.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

DECLARE @var varchar(10), @money money

SET @var = ($111.11)

SET @money = -1* CAST(CASE WHEN LEFT(@var,1) <> '(' THEN @var

ELSE SUBSTRING(@var, 2, LEN(@var) - 1)

END AS money)

PRINT @money

SET @var = $222.22

SET @money = CAST(CASE WHEN LEFT(@var,1) <> '(' THEN @var

ELSE SUBSTRING(@var, 2, LEN(@var) - 1)

END AS money)

PRINT @money

NOTE: this code does NOT guarantee that your varchar is castable as a money, and it WILL blowup if it is not!! It will also blow up if the trailing ')' is not there.

P.S. Once again I get to say that CASE is my favorite 4-letter TSQL word!! 😎

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

gtan018 SSChasing Mays Points: 624 More actions · Answer 3

Basically, I'm trying to convert a column that has a negative amount to be placed inside parentheses. So far I've been playing with the following: SELECT TransactionDate, Cast(Replace(Replace(TransactionValue,'-', '($'), '',')') as varchar) AS TransactionValue

FROM tblTransactionImport

So far the output looks like this:

($9306.78

($1.44

($1.05

($288.63

($300.53

($353.4

37.7

I need the missing ')' for negative amounts and the '$' for positive amounts. Maybe the CASE will work for the positive amounts.

Thank you.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

CRAP!! I went backwards!! Just a sec and I will have a fix ...

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

CASE WHEN @money < 0 THEN '(' + CAST(@money AS varchar(20)) + ')' ELSE CAST(@money AS varchar(20)) END

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service