Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2009-05-06
13
Medium Priority
?
314 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 43

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 43

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 43

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 43

Expert Comment

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

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 43

Accepted Solution

by:
pcelba earned 1500 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 43

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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

578 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