Solved

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

Posted on 2009-05-06
13
304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

707 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