How to Use Concatenation and Conversion SQL Server 6.5

Posted on 2011-05-11
Last Modified: 2012-08-14
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.


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.
Question by:kristian1979
    LVL 32

    Accepted Solution


    SET PN.PN_P_Notes = 'This is a test!' + cast(PN.PN_P_Notes as varchar(1024))
    LVL 32

    Expert Comment

    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
    LVL 1

    Author Comment

    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?
    LVL 32

    Assisted Solution

    select max(datalength(PN_P_Notes)) from PN
    LVL 1

    Author Closing Comment

    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))

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now