• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 958
  • Last Modified:

T-SQL - Concatenate Memo and Currency

Hello.  I have two fields, one memo and the other currency.  I need to concat both fields into a single field but I need the currency format for the PurchaseOrder.Amount to stay intact with comma separators.  My current code is shown below.  Problem here is the PurchaseOrder.Amount field looses all commas (i.e. $23335561.23)

Anybody have a relatively simple solution?

[NewMemo] = CAST(PurchaseOrder.Memo AS Nvarchar(2000))
                       + ' $'
                       + CAST((PurchaseOrder.Amount + tax1 + tax2 + tax3) AS nvarchar(20))
0
jobprojn
Asked:
jobprojn
  • 2
1 Solution
 
Shannon_LowderCommented:
Try the following  CONVERT(varchar, CONVERT(money,PurchaseOrder.Amount + tax1 + tax2 + tax3), 1).

---
Shannon Lowder
Database Engineer
http://toyboxcreations.net
0
 
imitchieCommented:
If your field is already a money type, you don't need to cast to money.

[NewMemo] = CAST(PurchaseOrder.Memo AS Nvarchar(2000))
                       + ' $'
                       + CONVERT(nvarchar(20), convert(money, PurchaseOrder.Amount + tax1 + tax2 + tax3), 1)
0
 
imitchieCommented:
Just seconds late...
I see Shannon has already given you the answer.  Convert allows you to specify the style, check BOL.
0
 
jobprojnAuthor Commented:
Beautiful.  Thanks.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now