sql - replace argument not valid

Question

I would like to replace the text of a column in a table

I tried out:

select replace([article], '<p>&nbsp;</p>', '') from Articles

update Articles
set article = replace(article, '<p>&nbsp;</p>', '')
where article like '<p>&nbsp;</p>'

or 

UPDATE [AJA].[dbo].[Articles]
   SET [article] = ' '
 WHERE [article] = '<p>&nbsp;</p>'
GO

and everytime it comes out with the error:

argument 1 not valid in replace

What's wrong with it?

Thanks for your help

Answer 1

I've check out your problem verifying with two datatype i.e.

1. ntext : while working with ntext , it throws above error....Check out here

2. varchar(max): While working with varchar(max), it is perfectly workin....Check out here

So, use varchar(max) datatype while working with html tag....

If you want to work on your previous type, then cast the column type as varchar

   SELECT REPLACE(CAST([article] as VARCHAR(MAX)), '<p>&nbsp;</p>', '')
   FROM Articles

Answer 2

You're getting this error because you've text datatype. With varchar datatype your query works fine.

You need to cast your field from text to varchar in order to use replace function.

Declare @mytable table
(
Article text
);

INSERT into @mytable VALUES('<p>&nbsp;</p>');
INSERT into @mytable VALUES('<p>&nbsp;</p>');
INSERT into @mytable VALUES('<p>&nbsp;</p>');
INSERT into @mytable VALUES('<b>&nbsp;</b>');


select replace(cast([article] as VARCHAR(8000)),'<p>&nbsp;</p>','')
from   @mytable
where Article LIKE '<p>&nbsp;</p>'