You can not use REPLACE with Text, local variables are OK :-)
BTW why you need Text? Aren't varchar(4000) enough? So much troubles with Text types...
Yes with cast you could do that, but with CAST you could truncate your text if it is longer that 8000 bites:

UPDATE #TestTable SET ColText = CAST(REPLACE(CAST(ColText as varchar(8000)),'START TAG','NEW TAG') AS Text)

If you are not sure that Text is smaller than 8000 bite you could use cursor and UPDATETEXT statement.


>Now, how to with a text field type in SQLServer 2000? Use cast()? I guess I cannot use local variables with text types?
>
>
>>You were too focused on VFP side, I think.
>>That happens to me all the time (especially when I put [;] as continuous line in my SQLEXEC() strings)
>>:-)))
>>
>>
>>>Sheesh! What was I thinking????
>>>
>>>Thanks so much Borislav!
>>>
>>>

>>>select * from agency
>>>DECLARE @lcOldString varchar(200)
>>>SET @lcOldString = 'HIGH POINT'
>>>DECLARE @lcNewString varchar(200)
>>>SET @lcNewString = 'TRACY'
>>>update agency set agencyname = REPLACE(agencyname,@lcOldString,@lcNewString)
>>>select * from agency
>>>

>>>
>>>>

>>>>DECLARE @lcstring varchar(200)
>>>>DECLARE @lcnewstring varchar(200)
>>>>select * from agency
>>>>SET @lcstring = 'HIGH POINT'
>>>>SET @lcnewstring = 'TRACY'
>>>>update agency set agencyname = REPLACE(agencyname,@lcstring,@lcnewstring)
>>>>select * from agency
>>>>

>>>>
>>>>
>>>>
>>>>>OK, in SQL Management Studio, how do I set the vars? I have reached a brain cramp and can't believe I cannot think of it. it doesn't like this:
>>>>>
>>>>>

>>>>>select * from agency
>>>>>SET	lcstring = 'HIGH POINT'
>>>>>SET lcnewstring = 'TRACY'
>>>>>update agency set agencyname = REPLACE(agencyname,lcstring,lcnewstring)
>>>>>select * from agency
>>>>>

>>>>>or
>>>>>

>>>>>select * from agency
>>>>>DECLARE lcstring = 'HIGH POINT'
>>>>>DECLARE lcnewstring = 'TRACY'
>>>>>update agency set agencyname = REPLACE(agencyname,lcstring,lcnewstring)
>>>>>select * from agency
>>>>>

>>>>>
>>>>>>>

>>>>>>>lcoldstring = 'old value'
>>>>>>>lcnewstring = 'new value'
>>>>>>>UPDATE vhstatus SET vhnote = STRTRAN(vhnote,lcoldstring,lcnewstring)
>>>>>>>

>>>>>>>
>>>>>>>Any idea how to do that in SQLServer 2000 (Not vfp will be using sqlexec())?
>>>>>>

>>>>>>lcoldstring = 'old value'
>>>>>>lcnewstring = 'new value'
>>>>>>UPDATE vhstatus SET vhnote = REPLACE(vhnote,lcoldstring,lcnewstring)
>>>>>>