Concatenate Varchar + Text Invalid operator type error

[bmacbmac]

Hi... In mytable I have:
COl1 Text
COL2 Varchar(255)

I am trying to concatenate the two together in an update statement for COL1.

I have tried:

update mytable
set COL1 =  COL1 + ' ' + convert(text, COL2)

and

update mytable
set COL1 =  COL1 + ' ' cast(COL2 as text)

Both options give me the error:
Invalid operator for data type. Operator equals add, type equals text.

Is there a different way I should go about this?

 

[bborissov]

What version of SQL Server you use?
If you use SQL Server 2005 or 2008 I strongly recommend you to change field types from TEXT to n/varchar(max).

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[djj55]

If you cannot change type then to add to a text field you will need the updatetext function.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[markros]

As Boris asked, what is your SQL Server version? In SQL Server 2005 and above

select col1 = cast(col1 as varchar(max)) + cast(col2 as varchar(max))

PluralSight Learning Library

 

[bmacbmac]

I'm on SQL 2000 so I guess updatetext is my only option. I'm having a difficult time with the variables. Still trying.

 

[bmacbmac]

I can't change the COL1 to Varchar because of the front end application and developers will kill me. I ended up merging them in a temp table as Varchar and then updated my table joining the temp table.

create table #temp5 (ilandid int, test text)
insert into #temp5
select ilandid, cast(col1 as varchar(255)) + ' ~ ' + COL2
from mytable

update mytable
set col1 = test
from mytable
join #temp5 on #temp5.ilandid = mytable.ilandid

 

[SQLSister]

YOu know you will get truncated results with that right?


"NOTHING is more important in a database than integrity." ESquared

 

[bborissov]

CAST to varchar(255) !!!!!!!!!!!!!!!!!!!!!!!
You have to store not more than 255 chars in that column and you use TEXT!!!!!!!!!!!!!!!!!!!!!!!!
Grrrrr!!!!!
Use varchar(8000) then.
The front end should didn't knew about that change (test this of course)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[SQLBill]

I really hope you made a backup of the table before running that. As SQLSister and BBorissov said, your end result may not be what you expected and you may have lost data. Now that could really irritate users.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[bmacbmac]

I thought about that so I ran

select datalength(col1), datalength(col2)

And found the longest length for both fields was not >= 255 so I should be safe.(?)

Would it have given me a truncate error had the data truncated?