Concatenation Problem

Question

Post reply

Concatenation Problem

thgamble1

Ten Centuries

Points: 1212
More actions
February 18, 2005 at 12:09 pm

#163693

The results I have outputted currently are:
07/07/2004 25.00 25.00
What I want though is for them to come out as:
07/07/2004 25 25
The string that I'm making is derived from 3 fields which I am concatenating together.
sold_date (type datetime)
amountsold (type money)
amountpaid (type money)
What I need is an update query that will concatenate the 3 fields together as a string, but so far am running into a stumbling block in that nothing I do is working so far. Anyone got an idea for me?
-Thgamble1

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

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

Osoba SSCommitted Points: 1964 More actions · Answer 1

I would cast the value to an int then to varchar for the string concat:

cast(cast([money] as int)as varchar(2))

..probably faster more efficient ways but this should give you the idea.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

maybe a left() operation could be faster... but the question is : Is it always ok the truncate the decimal part or do you need to keep it if it's not all zeros?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

I'm sure you have pretty good reasons for this intentional denormalization, don't you?

Anyway, as Remi already mentioned, if you don't care about truncation and rounding isn't an issue either, see if this helps

DECLARE @m1 MONEY, @m2 MONEY

SELECT @m1 = 25.25, @m2 = 24.95

SELECT STR(@m1,2)+' '+ STR(@m2,2)

------

25 25

(1 row(s) affected)

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

thgamble1 Ten Centuries Points: 1212 More actions · Answer 4

What I'm trying to do is update a field in a database to display a concatenated string to my agents for a sales type campaign that we are doing.

The information that is displayed is simply "Pledge Date" / "Pledge Amt" / "Paid Amt" so that my agents can know better how much to try to pitch the customers. It's not used for anything other than display, and it's always in whole dollars rather than decimals. The problem though is that while most of the time the max amount displayed is <100 there are occassions when it is greater, and thus I can't simply put in a substring type update to only give me the first 2 characters.

And when I try to modify the select statements from Frank and others, I am doing something wrong because I keep getting nothing but the database trying to concatenate the 3 seperate fields into a datetime format rather than strictly characters put together.

My exact update string is:

Update L set L.[Pledge Line 1] = ((convert(varchar(10),[Invoice Date],101)) + ' ' + (convert(varchar(10),[Pledge 1])) + ' ' + (convert(varchar(10),[Amt Paid])))

from [Lead_Management_Preparation Table] L where L.[Invoice Date] <> '1/1/1900'

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Have you try with STR() yet?

This works for me:

ALTER TABLE Orders ADD testME VARCHAR(30)

GO

USE NORTHWIND

GO

UPDATE Orders SET testME =

CONVERT(VARCHAR(10), OrderDate,101) +

' ' +

STR(Freight,2 ) FROM Orders WHERE OrderDate = '19960704'

SELECT testME FROM Orders

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]