UPDATING a current recordset...help

medar

I have a message board that is a bit screwy with older posts, so I thought I would update some of the 'messed up' HTML.

In the content of some posts, I have the following: &lt;br&gt;

This should be: <br>

How do I write the UPDATE to change just this string and not affect the rest of the content of the field?

UPDATE table_name SET field_1 = '<br>' WHERE field_1 = '&lt;br&gt;'

This will not work, as it will find nothing, and change nothing.

UPDATE table_name SET field_1 = '<br>' WHERE field_1 = '%&lt;br&gt;%'

This would possibly mess up the entire post...

Help?

Anon

This would be a lot like str_replace(). I do not think that SQL allows you to search through records and update only parts of entries based on search criteria. If it does, then my apology.

However, another option is to use str_replace(). The logic would look something like below:

Query db (SELECT field_1 FROM table_name)
Iterate through the results. Test for existence of bad content.
If content is bad, update the content with str_replace then enter it back into the system with an insert statement.

Now the issues with the above are that if your database is huge, this is going to take some time. Is it local (home or something) or on a web server? If you can't tie up too much time without making someone mad, do a little at a time and spread it out over a couple of days. You could use the limit statement on the first query.

Hope that helps,
Big Din K.R.