[quoted text, click to view]

Hi 1idesigned,

The REPLACE function can't be used for text or ntext data.

If your data holds less than 4000 positions, you can cast it to nvarchar
before doing the replacing, as showns in the example below:

create table test (pk int not null primary key,
textcol ntext)
go
insert into test (pk, textcol)
select 1, N'This is a sample text'
insert into test (pk, textcol)
select 2, N'And another row of nonsense'
go
-- This will fail
select pk, replace(replace(textcol, 'a', ''), 'e', '')
from test
go
-- This will work; result is nvarchar(4000), not ntext
select pk, replace(replace(cast(textcol as nvarchar(4000)), 'a', ''),
'e', '')
from test
go
-- Same as above, but result converted back to ntext
select pk, cast(replace(replace(cast(textcol as nvarchar(4000)), 'a',
''), 'e', '') as ntext)
from test
go
drop table test
go


If your data may exceed 4000 position, I guess you'll either have to use
READTEXT, UPDATETEXT and WRITETEXT to handle the data in 4000-byte
chunks, or use PATINDEX, SUBSTRING and + (concatenation) to find and
replace the tags. In both cases, you'll have to implement some looping.

Best, Hugo
--