Using PhpMyAdmin (or Mysql) for a find and replace operation

Hi Folks,

I have located the database table and column that contains a combined text and html phrase that I would simply like to delete (for now).

I suspect i'm not using Phpmyadmin's "Find and Replace option properly - the exact content I want removed are:

"Is this tape helpful for AA Newcomers?  If so, please<strong> add some Comments</strong> about this tape's topics? (e.g., "For Newcomers" "Step 1", "Inventory", "Spirituality", etc?)

Your comments will be used to tag and categorize the tape properly.

<strong>THANK YOU!!</strong> :)

How might i do this through "find and replace" or construct a proper mysql statement to remove this?

Thank you!
John HGeneral TinkererAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

First of all, do you want to replace that column's content with spaces or do you want to delete the entire row that contains it?

Does a query that looks for "Is this tape helpful for AA Newcomers?" match this row and ONLY this row?  If so then you don't need to search for that entire long string.  If not, is there another column that contains a value that would uniquely match this row, and ONLY this row, if it were used as an additional search criterion?

We would need answers to these questions in order to construct the correct query to accomplish your goal.  Knowing the column name(s) would be beneficial as well otherwise you will likely get a rather generic solution offered.
Dave BaldwinFixer of ProblemsCommented:
Databases don't have 'Find and Replace' functions.  I have never seen such a thing in the many versions of phpMyAdmin that I use.  I would first use the search function on that table to find out how many rows have this text.  If there is only 1, you can manually edit it.
I forgot to post a sample query that you could modify for your table to do the search...

  select * from tblname where txtfield like  "Is this tape helpful for AA Newcomers?%";

notice the "%" character at the end of the string in the where clause,,, it is a wildcard pattern that says find all rows with txtfield that begin with the partial string in front of it.
5 Ways Acronis Skyrockets Your Data Protection

Risks to data security are risks to business continuity. Businesses need to know what these risks look like – and where they can turn for help.
Check our newest E-Book and learn how you can differentiate your data protection business with advanced cloud solutions Acronis delivers

In short it is a two step, you first pull the column into a variable, you then change the variable content to say what you want, then update the column.

If you want to change it in its entirety run
update tablename set column='your desired value' where column like '%pattern uniquely identifying%' or idolumn=specific_id_of_row

Open in new window

John HGeneral TinkererAuthor Commented:
Thanks Jester - I think we're heading in the right direction.

With your above SELECT query, it came back with some 3300 rows affected - but there was no option to update or delete that string.

So I tried DELETE FROM `wp_zt8sq9_posts` WHERE post_content like  "Is this tape helpful for AA Newcomers?%";....   And a bunch of posts were not showing up on the site and I did catch one 404'ing.  I've since imported the old database and everything is back to normal.

Should I be using a different query string other than DELETE?

@ Arnold - lemme tinker with your query...
Do not do delete as it removes the entire row, if you run, your need is to run update that modifies a column.
The selects are a must to make sure your search criteria is narrow enough to match one or a few rows with data......

Could you provide an example of what the result of the modification you want to end up with.
Having this is some stuff that I have
Having stuff
You need to find another column whose value along with the txtfield value will form a unique combination.

Then, you could use a select such as this to confirm that only 1 row is returned from the select query BEFORE changing it to an update query...

select * from tblname where somecolumn = 'somevalue' and txtfield like 'Is this tape helpful for AA Newcomers?%';
Dave BaldwinFixer of ProblemsCommented:
They are correct, UPDATE is the function you want.  You can use it to replace the text you want to get rid of with something else without DELETE-ing the rows.
John HGeneral TinkererAuthor Commented:
OOps on Delete :/

Current:  I have unwanted text in the body of thousands of posts in wordpress.  This text/hmtl is located in the table "wp_zt8sq9_posts", column name "post_content",  and the content I want removed begins with: "Is this tape helpful for AA Newcomers?..........(with a bunch text and some html after it)"

What I want is to clear/delete all instances of text/html in that column that begins with ""Is this tape helpful for AA Newcomers?........"  so that it is no longer printed on the posts that currently contain that text.

Thanks a bunch for helping me with this...
update wp_zt8sq9_posts set post_content = '' where post_content like 'Is this tape helpful for AA Newcomers?%';

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John HGeneral TinkererAuthor Commented:
Gents, thank you so much for your patience, super-helpful, and I understand it, which is even better.  5000+ posts just got irrelevant/duplicate text removed :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.