CODEWITHSUNDEEP
sqlsql-servert-sqlreplacefind
reffer
reffer

Reputation: 11

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 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?

Upvotes: 0

Views: 508

Answers (4)

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

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

Upvotes: 3

Brett
Brett

Reputation: 4061

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...

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332661

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.

Upvotes: 4

Bernard
Bernard

Reputation: 7961

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.

Upvotes: 0

Related Questions