i have 500 records in a table. one column holds all html data. so e.g -
html> body> ... /body> /html>.
What i want to do is a find and replace. i have this tag in every single record -
<table id="something something" />
i want to replace that in all the rows with <table id="" />
now the difficult part is all the "something something" is different for each and every alt.
So the only common tag is "table id=". how can do a find an replace here?
views:
49answers:
4
A:
You will likely need to write a stored procedure to do this, a combination of a SELECT statement to find and an UPDATE statement to replace.
Bernard
2010-08-24 20:00:04
logically i know how to do it. anyone knows. do u have an example of the syntax i need to use? if not, please dont answer this question
reffer
2010-08-24 20:02:54
+4
A:
Use the REPLACE function:
UPDATE YOUR_TABLE
SET html_data = REPLACE(html_data,
'<table id="something something" />',
'<table id="" />')
the difficult part is all the "something something" is different for each and every alt.
SQL Server 2005+ has CLR functionality, which you'd need to use to create a regex replace function in order to be more accommodating as a single query. See this page for both a downloadable script, and the source code.
For SQL Server versions prior to that, you might just be better off getting the content to a text file & updating the content via regex/etc, for overwriting the existing content.
OMG Ponies
2010-08-24 20:03:41
+1
A:
If you write a cursor function to go through each row of the table (very inefficient, but I'm guessing that you're only doing this once?)
Then do a replace on that string:
SELECT REPLACE(@HTMLText,'table id="%" />','table id=""');
I think that should do what you need, and here is basic cursor functionality if you need it: http://blog.sqlauthority.com/2007/01/01/sql-server-simple-example-of-cursor/
EDIT: Actually, I tested a bit more, and I can't get it to accept the wildcard, although it doesn't complain, I don't see it functioning properly...
Brett
2010-08-24 20:06:34
i like this idea but for some reason nothing happened. it came back with the existing row that was before replace.
reffer
2010-08-24 20:14:57
this is my syntax -select REPLACE(html,'table id="%" />','table id=""') from Articleswhere id=101.. html is name of the field.
reffer
2010-08-24 20:16:10
Yeah, I'm sorry, I thought I had the wildcard working, but unfortunately the REPLACE() function does not take a wildcard...
Brett
2010-08-24 20:32:22
+3
A:
Try this (assumes only one "table id" occurs in each):
declare @TestTable table (
html_data varchar(100)
)
insert into @TestTable
(html_data)
select '<html><body><table id="something something" /></body></html>'
union all
select '<html><body><table id="something different" /></body></html>'
select html_data from @TestTable
update t
set html_data = LEFT(t.html_data, CHARINDEX('<table id="', t.html_data)-1)
+ '<table id="" />'
+ RIGHT(t.html_data, LEN(t.html_data) - CHARINDEX(' />', t.html_data, CHARINDEX('<table id="', t.html_data)) - 2)
from @TestTable t
select html_data from @TestTable
EDIT: Based on feedback in the comments below, this modified code should work.
declare @TestTable table (
html_data varchar(100)
)
insert into @TestTable
(html_data)
select '<html><body><table id="xxx"><tr><td></td></tr></table>... </body></html>'
select html_data from @TestTable
update t
set html_data = LEFT(t.html_data, CHARINDEX('<table id="', t.html_data)-1)
+ '<table id="">'
+ right(t.html_data, LEN(t.html_data) - CHARINDEX('>', t.html_data, CHARINDEX('<table id="', t.html_data)))
from @TestTable t
select html_data from @TestTable
Joe Stefanelli
2010-08-24 20:22:44
it added a "<table id="">" before the existing "<table id="eded">" looks good so far but how do i remove existing or replace existing?
reffer
2010-08-24 20:33:41
<html><body><table id="xxx"><tr><td></td></tr></table>... more tables and tr's and td's. </body></html>. so this is 1 row in the field html of the table in sql server. i hope that answers. thanks for all the help
reffer
2010-08-24 20:53:11
This is substantially different than what you originally posted `<table id="something something" />`. The code I wrote was dependent on the existence of the terminating `/>` and will not work in light of this data.
Joe Stefanelli
2010-08-24 21:04:49
I've added a second code block to my answer that should handle the case you've posted in your comment.
Joe Stefanelli
2010-08-24 21:11:07
perfect. man you're good. thank you so much. for some reason its not letting me pick top answer
reffer
2010-08-24 21:28:47
You should be able to click on the outlined check mark to accept this answer.
Joe Stefanelli
2010-08-24 21:30:29