replace string in sql
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 a开发者_运维知识库nd 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?
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.
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
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...
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.
加载中,请稍侯......
精彩评论