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
Solved

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

Posted on 2009-05-06
13
287 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
t-sql need help on t-sql 10 26
Stored Proc - Rewrite 42 59
SQL Quer 4 22
Remove the Middle name initials and the suffixes from a string in SQL Server 4 21
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 …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

838 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