Help concat strings with a money column

Question

Post reply

Help concat strings with a money column

Jeff-132889

Ten Centuries

Points: 1112
More actions
April 20, 2006 at 12:03 pm

#114430

I need some advice on this one.............
I want to concatonate a bunch of fields to be one field such as:
SET concat_null_yields_null OFF
SELECT
'Vendor: '+ vendor + '/n ' +
'Check number: ' + [check] + '/n ' +
'Amount: ' + str(amount) + '/n ' +
'Account number: ' + str(actnum)
The column 'amount' is originally a money field but str'd so that I can concatonate it with everthing else. I do however need it to ultimatley appear as $1,234.00 not the current 123400.............

Viewing 9 posts - 1 through 8 (of 8 total)

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

Pam Brisjar SSChampion Points: 12094 More actions · Answer 1

Start here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 2

You could do that kind of formatting at the front end.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

vadba SSChampion Points: 11133 More actions · Answer 3

Try this:

SELECT 'Vendor: '+ vendor + '/n '

+ 'Check number: ' + [check] + '/n '

+ 'Amount: $' + CONVERT(varchar(15), amount, 1) + '/n '

+ 'Account number: ' + str(actnum)

Jeff-132889 Ten Centuries Points: 1112 More actions · Answer 4

Jeff-132889

Ten Centuries

Points: 1112

April 21, 2006 at 8:23 am

#633518

Thanks Oldhand that's perfect!

Jeff

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 5

Just curious... what do YOU do when there is no front end as in when you're exporting files that have to meet certain formatting criteria?

--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)

Jeff-132889 Ten Centuries Points: 1112 More actions · Answer 6

Jeff,

That's exactly what I'm up against. I have to do everything in t-sql to create a .csv that's formatted properly. Oldhand's solution is what I needed. I just concatonated a '$' in front of it and did a case when so I don't get and '$0.00' results.

Jeff

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 7

Jeff,

Actually, "Old Hand" is a status, not a name. MKEast gave you that fine solution. And, I sympathize with you... have had to do much formatting in SQL Server for the exact same reason.

Anyway, thanks for the feedback.