Solved

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

Posted on 2009-05-06
13
294 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:NickHoward
  • 7
  • 5
13 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24318304

UPDATE urTable
SET TextCol = LEFT (Textcol, CHARINDEX('<', Textcol )-1 )
WHERE TextCol is not null
0
 
LVL 42

Expert Comment

by:pcelba
ID: 24318358
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
 

Author Comment

by:NickHoward
ID: 24318980
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 42

Expert Comment

by:pcelba
ID: 24319033
cdescription is text, so the STUFF does not support it. What SQL version do you have?
0
 

Author Comment

by:NickHoward
ID: 24319062
SQL 2005
0
 
LVL 42

Expert Comment

by:pcelba
ID: 24319120
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
 

Author Comment

by:NickHoward
ID: 24319299
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
 
LVL 42

Expert Comment

by:pcelba
ID: 24319621
Do you have max one or more occurences of this text in cdescription?
0
 
LVL 42

Expert Comment

by:pcelba
ID: 24319690
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
 

Author Comment

by:NickHoward
ID: 24319810
You are absolutely right. I have several occurences. Wonder how we can loop it?
Thanks.
0
 
LVL 42

Accepted Solution

by:
pcelba earned 500 total points
ID: 24319951
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
 
LVL 42

Expert Comment

by:pcelba
ID: 24320521
Sorry, I wrote "not slow" but it should be "slow" ...
0
 

Author Closing Comment

by:NickHoward
ID: 31578658
Helped to some extent. Thanks.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question