Post reply

Help concat strings with a money column

  • 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.............

  • April 20, 2006 at 12:12 pm

    #633347

    Start here:

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

  • April 20, 2006 at 12:33 pm

    #633354

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

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

  • April 21, 2006 at 8:16 am

    #633514

    Try this:

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

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

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

         + 'Account number: ' + str(actnum)

     

  • April 21, 2006 at 8:23 am

    #633518

    Thanks Oldhand that's perfect!

    Jeff

     

  • April 22, 2006 at 11:13 am

    #633654

    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)
    Intro to Tally Tables and Functions

  • April 22, 2006 at 11:44 am

    #633658

     

    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

     

     

     

  • April 22, 2006 at 9:08 pm

    #633669

    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.

    --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)
    Intro to Tally Tables and Functions

  • April 23, 2006 at 11:09 am

    #633683

     

    Thanks MKEast !!!!!!!!!!!!!!!!!!!!!!!

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

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