Replace syntax error

Hello

In my database table I have replaced <h3> Some Text </h3> with <h2> Some Text </h2> in the Description column as below:

UPDATE CAT_Products

SET Description = replace (Description, '</h3>', '</h2>')
WHERE Description <> '</h2>'

UPDATE CAT_Products
SET Description = replace (Description, '<h3>', '<h2>')
WHERE Description <> '<h2>'

However, when I try the same replace syntax code with the DescriptionHTML column in the same table, it does not work, e.g.

UPDATE CAT_Products
SET DescriptionHTML = replace (DescriptionHTML, ' <h3> ', '<h2>')
WHERE Description <> '<h2>'

What do I need to adjust?

Thanks

can you explain what you mean by not working? also pls provide some sample data you have that you are expecting the REPLACE to happen on.

|||

Hello ndinaker

When I put in this code:

UPDATE CAT_Products
SET DescriptionHTML = replace (DescriptionHTML, ' <h3> ', '<h2>')
WHERE Description <> '<h2>'

I dose not execute and I get this error message instead:

[Error] Script lines: 1-5 --------
Argument data type ntext is invalid for argument 1 of replace function.

[Executed: 12/04/07 19:17:21 BST ] [Execution: 0/ms]

As in the text Description column, I am trying to update the <h3> and </h3> to <h2> and </h2> for example: <h3> Some Text </h3> to be replaced by <h2> Some Text </h2>

However, as it is written in html format in the DescriptionHTML database column it looks like:

<h3> Some Text </h 3> to be replaced by <h 2> Some Text </h2>

Thanks

|||

I think the pattern you are matching is only for <h3> and not </h3>. So you probably need to a double replace.

declare

@.str varchar(100)

set

@.str='<h3> Some Text </h3>'

--final value = <h 2> Some Text </h2>

select

@.str,replace(@.str,'<h3>','<h2>'),replace(replace(@.str,'<h3>','<h2>'),'</h3>','</h2>')

|||

Hi ndinakar

Thanks for the code, but I needed to change:

@.str varchar(100)

To

@.str ntext

H

owever this change cause the code to fail:

UPDATE CAT_Products

@.str ntext
set

@.str = '<h3> * </h3>'
--final value = <h 2> * </h2>

select

@.str, replace(@.str,'<h3>', '<h2>'), replace( replace(@.str,'<h3>', '<h2>'), '</h3>', '</h2>')

I got this error message:

[Error] Script lines: 1-13 --------
Line 4: Incorrect syntax near'@.str'.

More exceptions ... Must declare the variable'@.str'.

[Executed: 12/04/07 21:49:22 BST ] [Execution: 0/ms]

|||

Try if this works:

UPDATE

CAT_Products

set

DescriptionHTML=replace(replace(DescriptionHTML,'<h3>','<h2>'),'</h3>','</h2>')

WHERE

Description<>'<h2>'

|||

Before you try the update try a SELECT first to see what records are getting affected and how.

SELECT

NewDescriptionHTML=replace(replace(DescriptionHTML,'<h3>','<h2>'),'</h3>','</h2>'),

DescriptionHTML

FROM

CAT_Products

WHERE

Description<>'<h2>'

|||

As suggested, I tried this first:

SELECT

NewDescriptionHTML = replace( replace(DescriptionHTML,'<h3>', '<h2>'), '</h3>', '</h2>'),
DescriptionHTML

FROM

CAT_Products
WHERE

DescriptionHTML <> '<h2>'

But I got this error message:

[Error] Script lines: 1-11 --------
Argument data type ntext is invalid for argument 1 of replace function.

More exceptions ... The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

[Executed: 12/04/07 23:40:42 BST ] [Execution: 0/ms]

Then I tried this:

UPDATE

CAT_Products
set

DescriptionHTML = replace( replace(DescriptionHTML,'<h3>', '<h2>'), '</h3>', '</h2>')
WHERE

DescriptionHTML <> '<h2>'

But I got this error message:

[Error] Script lines: 1-11 --------
Argument data type ntext is invalid for argument 1 of replace function.

More exceptions ... The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

[Executed: 12/04/07 23:43:54 BST ] [Execution: 0/ms]

It looks like I will have to update this column manually, I had best roll up my sleeves.

Thanks

|||Ahhhh...its the NTEXT. Its going to be a pain to retrieve those values/update them through the query analyzer. Perhaps you can write a little tool..a VB tool with a form...that retrieves the values into a textbox and a button to update it..that will be easier..|||

Try this:

SELECT

NewDescriptionHTML

=replace(replace(convert(varchar(4000),DescriptionHTML),'<h3>','<h2>'),'</h3>','</h2>'),

DescriptionHTML

FROM

CAT_Products

WHERE

Description<>'<h2>'

|||

Hi ndinakar

Thanks again for your help and support

Yes you are right the Ntext is a pain, cos when I tried your latest code suggestion I got this error:

[Error] Script lines: 1-15 --------
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

[Executed: 13/04/07 09:17:43 BST ] [Execution: 0/ms]

It certainly dose not like the NTEXT.

A cup of coffee and rolling up of the sleeves coming up.

Thanks

sql