Removing part of text in column based on find and replace query

I have table called products and field cdescription where my data is mix of text and some wiered tags which I want to remove.

For example, a record looks like:

cdescription = This is best book <input id="gwProxy" type="hidden">

I want this to become:

cdescription = This is best book

Similary query should remove <input id="gwProxy" type="hidden"> from all records in cdescription without disturbing text data.

Thanks for your help.
NickHowardAsked:
Who is Participating?
 
pcelbaCommented:
The loop is easy but not slow probably:

WHILE EXISTS (SELECT cdescription FROM products3 WHERE charindex('<',cdescription ) > 0 AND charindex('>',cdescription ) > charindex('<',cdescription))
  UPDATE products3 SET cdescription = stuff(CAST(cdescription AS varchar(max)) , charindex('<',cdescription ), charindex('>',cdescription )-charindex('<',cdescription )+1,'')
WHERE cdescription  IS NOT NULL and charindex('<',cdescription ) > 0 AND charindex('>',cdescription ) > charindex('<',cdescription)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:

UPDATE urTable
SET TextCol = LEFT (Textcol, CHARINDEX('<', Textcol )-1 )
WHERE TextCol is not null
0
 
pcelbaCommented:
This will remove tags from your texts:

UPDATE tbl SET col = stuff(col, charindex('<',col), charindex('>',col)-charindex('<',col)+1,'')
WHERE col IS NOT NULL and charindex('<',col) > 0 AND charindex('>',col) > charindex('<',col)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
NickHowardAuthor Commented:
I tried it:

UPDATE products3 SET cdescription = stuff(cdescription , charindex('<',cdescription ), charindex('>',cdescription )-charindex('<',cdescription )+1,'')
WHERE cdescription  IS NOT NULL and charindex('<',cdescription ) > 0 AND charindex('>',cdescription ) > charindex('<',cdescription)

But got SQL error:

 37000(8116)[Microsoft][ODBC SQL Server Driver][SQL Server]Argument data type text is invalid for argument 1 of stuff function.
37000(8180)[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (0.00 secs)

Pls advice.
0
 
pcelbaCommented:
cdescription is text, so the STUFF does not support it. What SQL version do you have?
0
 
NickHowardAuthor Commented:
SQL 2005
0
 
pcelbaCommented:
This works on SQL 2005:

UPDATE products3 SET cdescription = stuff(CAST(cdescription AS varchar(max)) , charindex('<',cdescription ), charindex('>',cdescription )-charindex('<',cdescription )+1,'')
WHERE cdescription  IS NOT NULL and charindex('<',cdescription ) > 0 AND charindex('>',cdescription ) > charindex('<',cdescription)

If you have Unicode characters in the text column you should use nvarchar.
0
 
NickHowardAuthor Commented:
Thanks. Now there is no error. However the text i want to remove
<input id="gwProxy" type="hidden">
did not remove from the cdescription field data.

Am I missing something?
0
 
pcelbaCommented:
Do you have max one or more occurences of this text in cdescription?
0
 
pcelbaCommented:
It works for me. If you have just several occurences of this text in each cdescription simply execute the UPDATE several times. If you have many occurences in each cdescription then it would be better to do it in a loop.
0
 
NickHowardAuthor Commented:
You are absolutely right. I have several occurences. Wonder how we can loop it?
Thanks.
0
 
pcelbaCommented:
Sorry, I wrote "not slow" but it should be "slow" ...
0
 
NickHowardAuthor Commented:
Helped to some extent. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.