[thelist] updating two text fields in MSSQL

[Joshua Olson]

> -----Original Message-----
> From: Canfield, Joel
> Sent: Monday, March 13, 2006 3:02 PM
> 
> We have two free-form text fields in a web tool, writing to 
> TEXT fields in MSSQL (fieldnames DESCRIPT and NOTE.)
> 
> I need to make DESCRIPT = DESCRIPT + ' Original Request: ' + NOTE, and
> make NOTE = Left(NOTE,256)
> 
> Except, it looks like I'd have to use UPDATETEXT or something 
> funky like that because the + string concatenator doesn't work with 
> TEXT data types.
> 
> Any suggestions?

Spin,

Do you have any idea what the maximum string length is within the two
fields?  If they are relatively short... say, 5000 characters or less, you
can execute a casting call [0] within the UPDATE query.  There's even a
chance (I haven't tested this) that it may work with much longer strings as
well.  For example:

UPDATE table
SET DESCRIPT = Cast(DESCRIPT AS varchar(5000)) + ' Original Request: ' +
Cast(NOTE AS varchar(5000))
, NOTE = Cast(NOTE AS varchar(256))

- OR -

UPDATE table
SET DESCRIPT = Convert(varchar(5000), DESCRIPT) + ' Original Request: ' +
Convert(varchar(5000), NOTE)
, NOTE = Convert(varchar(256), NOTE)

[0]
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
ca-co_2f3o.asp

<><><><><><><><><><>
Joshua L. Olson
WAE Technologies, Inc.
http://www.waetech.com/
Phone: 706.210.0168
Fax: 413.812.4864

Monitor bandwidth usage on IIS6 in real-time:
http://www.waetech.com/services/iisbm/