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

How to Use Concatenation and Conversion SQL Server 6.5

I have a SQL Server 6.5 database I'm trying to update.  I have a table called P_Notes and a column within that table called PN_P_Notes.  I want to add a note to the already exsisting notes.  Here is the query I am trying.

UPDATE P_Notes PN

SET PN.PN_P_Notes = 'This is a test!' + PN.PN_P_Notes


This is the error message I receive:

The data types varchar and text are incompatible in the add operator.

My column PN_Notes is the text data type.  So that makes the data I'm trying to add the varchar.

I guess I'm not sure on how to convert 'This is a test!' to text.

Please help.  I'm sure this is somewhat simple.  Thanks.
0
kristian1979
Asked:
kristian1979
  • 3
  • 2
2 Solutions
 
Ephraim WangoyaCommented:

SET PN.PN_P_Notes = 'This is a test!' + cast(PN.PN_P_Notes as varchar(1024))
0
 
Ephraim WangoyaCommented:
Note: 1024 is just an arbitrary value I picked up. use a value that properly represents the length of your longest note but not exceeding 8000
0
 
kristian1979Author Commented:
Hey thanks!  I think this is going to work.  Before I grant the points.  How would I find out the lenght of my longest note?
0
 
Ephraim WangoyaCommented:
select max(datalength(PN_P_Notes)) from PN
0
 
kristian1979Author Commented:
Thanks ewangoya.  This did exactly what I needed it to do.  

Just as a referrence to others, I needed a line break so  I also had to add + char(13) + between the to items I wanted.  The SET line ended up looking like this:  

SET PN.PN_P_Notes = 'This is a test!' + char(13) + cast(PN.PN_P_Notes as varchar(3000))
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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