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

What is the actual SQL Server error message? Can't see that in SELECT * FROM sys.messages WHERE text LIKE '%argument%not valid in%' — Martin Smith
– Martin Smith, Commented Aug 31, 2012 at 9:01
And those statements all do different things - the first should show you the correct data, but won't update it in the database; the second updates the article column removing the string you specify only on rows where article equals exactly <p> </p> (you're missing the wildcard characters on your like statement); the third one sets the article body equal to a space where article matches <p> </p> exactly. — Bridge
– Bridge, Commented Aug 31, 2012 at 9:04
If it is text datatype the full error message should tell you the problem at least on 2008 Argument data type text is invalid for argument 1 of replace function. — Martin Smith
– Martin Smith, Commented Aug 31, 2012 at 9:06

Akash KC · Accepted Answer · 2012-08-31 09:38:54Z

6

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

ntext : while working with ntext , it throws above error....Check out here
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

edited Aug 31, 2012 at 9:38

answered Aug 31, 2012 at 9:28

Akash KC

16.4k6 gold badges41 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dtjmsy Over a year ago

Good to know :), but I cannot change the design of the table, thanks

Akash KC Over a year ago

Go with updated answer....Cast the column type into varchar while using query....

Vishwanath Dalvi · Accepted Answer · 2012-08-31 09:26:35Z

3

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>'

edited Aug 31, 2012 at 9:26

answered Aug 31, 2012 at 9:01

Vishwanath Dalvi

36.9k41 gold badges128 silver badges156 bronze badges

1 Comment

devio Over a year ago

of course, this solution will cut off text longer than 8000 chars

kushalbhaktajoshi · Accepted Answer · 2012-08-31 09:07:46Z

0

Try this one

UPDATE Articles SET article = REPLACE(article, '<p>&nbsp;</p>', '')

Work on the other replaces likewise . .

answered Aug 31, 2012 at 9:07

kushalbhaktajoshi

4,6883 gold badges24 silver badges38 bronze badges

2 Comments

t-clausen.dk Over a year ago

This will update all rows even those where the text doesn't exist. Read @Bridge's comment

dtjmsy Over a year ago

Hi coosal, doesn' t work on me, same error invalid argument 1

Collectives™ on Stack Overflow

sql - replace argument not valid

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related