CODEWITHSUNDEEP
mysqlhtmldatabase
deathlock
deathlock

Reputation: 2837

What is the SQL for Find and Replace HTML Content?

So I have a database for a forum board, stored in a MySQL database called forum.

Inside it, I have a column called post in a table called forum_posts. There is some HTML I need to replace inside the post column, but it is stored between other characters (mostly text; more precisely, forum board conversations).

I have to replace this line:

<div width="95%" style="margin:8px 26px 4px 26px"><a href="javascript:void(0);" onclick="javascript:spoil(this);"><div class='spoilertop'><b>SPOILER</b> [Show/Hide]</div></a><div style="display: none;" class="spoilermain">

With this line:

<div width="95%" style="margin:8px 26px 4px 26px"><a class='spoilertop' style='display:block;'><b>SPOILER</b> [Show/Hide]</a><div class="spoilermain">

I have tried this SQL (and some of its variations), but it always results to MySQL error.

SELECT * FROM `forum_posts` REPLACE(`post`, `<div width="95%" style="margin:8px 26px 4px 26px"><a href="javascript:void(0);" onclick="javascript:spoil(this);"><div class='spoilertop'><b>SPOILER</b> [Show/Hide]</div></a><div style="display: none;" class="spoilermain">`, `<div width="95%" style="margin:8px 26px 4px 26px"><a class='spoilertop' style='display:block;'><b>SPOILER</b> [Show/Hide]</a><div class="spoilermain">`)

Is there any help?

Upvotes: 0

Views: 1778

Answers (2)

deathlock
deathlock

Reputation: 2837

Just a note if someone needs/wants to see what I did:

Update forum_posts
Set    post = replace(post, '<div width="95%" style="margin:8px 26px 4px 26px"><a href="javascript:void(0);" onclick="javascript:spoil(this);"><div class=\'spoilertop\'><b>SPOILER</b> [Show/Hide]</div></a><div style="display: none;" class="spoilermain">', '<div width="95%" style="margin:8px 26px 4px 26px"><a class=\'spoilertop\' style=\'display:block;\'><b>SPOILER</b> [Show/Hide]</a><div class="spoilermain">');
WHERE  post LIKE '%<div width="95%" style="margin:8px 26px 4px 26px"><a href="javascript:void(0);" onclick="javascript:spoil(this);"><div class=\'spoilertop\'><b>SPOILER</b> [Show/Hide]</div></a><div style="display: none;" class="spoilermain">%';

It was based on this: http://www.sqlteam.com/article/using-replace-in-an-update-statement

All credits goes to @Teez though!

Upvotes: 0

user319198
user319198

Reputation:

Quote strings properly.

Try below:

SELECT REPLACE('post', '<div width="95%" style="margin:8px 26px 4px 26px"><a href="javascript:void(0);" onclick="javascript:spoil(this);"><div class=\'spoilertop\'><b>SPOILER</b> [Show/Hide]</div></a><div style="display: none;" class="spoilermain">', '<div width="95%" style="margin:8px 26px 4px 26px"><a class=\'spoilertop\' style=\'display:block;\'><b>SPOILER</b> [Show/Hide]</a><div class="spoilermain">') as replacedpost FROM forum_posts;

Use UPDATE forum_posts SET post=REPLACE(...._) WHERE ... if you want to update in database.

Upvotes: 2

Related Questions