Find and replace LIKE sql data

Question

I'm tring to run a find and replace query on some sql data using Management Studio. I basically want to remove the word FREE from any content.

I tried running this query;

UPDATE    Table_1
SET              ContentDetails = REPLACE(ContentDetails, 'FREE', '')
WHERE     (ContentDetails LIKE '%FREE%')

But I get an error saying that data type text is invalid for argument 1 of replace function.

Answer 1

Try

UPDATE Table_1
SET    ContentDetails = REPLACE(CAST(ContentDetails as VARCHAR), 'FREE', '')
WHERE  (ContentDetails LIKE '%FREE%')

although it might cut the data off where the value is longer than what fits in a VARCHAR.

Answer 2

Updated due to comment

I think all you need to do is cast your ContentDetails field as a varchar with the length of the field i.e.

UPDATE Table_1
SET ContentDetails = REPLACE(CAST(ContentDetails as VARCHAR(100)), 'FREE', '')
WHERE ContentDetails LIKE '%FREE%'

Answer 3

Since you have a text column, you would need to use updatetext, which is painful, at best. However, you can cast contentdetails as a varchar(max), and you'll be peachy.

update table_1
set contentdetails = replace(cast(contentdetails as varchar(max)), 'FREE', '')
where contentdetails like '%FREE%'

Moreover, I highly recommend that you look into converting that column from text to varchar(max). It, along with ntext and image, is a currently deprecated data type, which will be removed at some point in the future of SQL Server.