Clever Geek Handbook

Find and replace LIKE sql data

I am trying to run a search and replace a query with some SQL data using Management Studio. I basically want to remove the word FREE from any content.

I tried this request;

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

But I am getting a message that the data type text is not valid for argument 1 of the replace function.

+2


source to share


3 answers


Since you have a column text

, you will need to use updatetext

, which is painful at best. However, you can use contentdetails

both varchar(max)

and you will be peaches.

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


Also, I highly recommend that you translate this column from text

to varchar(max)

. It, along with ntext

and image

, is currently a deprecated data type that will be removed at some point in the future by SQL Server.

+5


source


Try

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


although it can strip off data where the value is greater than what is appropriate in VARCHAR.

0


source


Updated due to comment

I think all you have to do is specify your ContentDetails field as varchar with ie field length

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


source






More articles:


All Articles