phpMyAdmin Find and Replace Built-in Feature without SQL

You need to Find and Replace some string of characters or numbers from a column in a MySQL database fast without writing an SQL command, maybe because you’re not so good at it or you can’t remember the command. I can’t say I’m in the same boat however I do find it easier to just point and click if I am able to.

phpMyAdmin is available on most hosts using MySQL and it is one of the easiest ways to administer your databases and its tables. There are a lot of features built-in, like search, insert and more.

The quick tip I have today is Find and Replace from a column within phpMyAdmin without writing any SQL commands, it’s easy to do and have a handy tip if it doesn’t work for you the first time.

To find and replace text, urls or whatever it is that you’re trying to replace from within phpMyAdmin, here are the steps:

1. Log into your phpMyAdmin (usually a link through your control panel like cPanel etc)
2. Select the database that has the table you want to find and replace from
3. From the list of tables within the chosen database, choose the table you want to find and replace
4. Once you’re inside click on the ‘Search’ tab
5. From within the search you should see the ‘Find and Replace’ button, click on that
6. In the first field labeled ‘Find:’ type in what you want to find and in the second field labeled ‘Replace with:’ type in what you want to replace it with
7. Make sure in the last field labeled ‘Column:’ you choose the column you are wanting to find and replace from e.g. if I want to replace all occurrences of ‘Hate’ with ‘Love’ from the category column I will enter Hate in the first field and Love in the second and choose category from the field labeled Column.

Here’s what it looks like if you’re still looking for it.

Click on Search from within the table to show find and replace option

Once you click the search tab you should see the 3 buttons the last one is Find and replace, click it to bring the following screen.

Fill in the fields to Find and replace from your chosen column/field within a table.

Once it successfully executes you’ll see the message with how many were found and replaced, prior to replacing there is a ‘Find and Replace’ preview which shows you a visual representation of what the rows look like before and after. Here’s the success message screen.

It’s showing that 378 rows were found and replaced successfully.

I replaced ’ with an apostrophe (').

Troubleshooting Find and Replace in phpMyAdmin

You get an error:

#1064 - You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near '‘%'

In the above I tried to find ‘ which is a left quote and replace it with an apostrophe, the problem is that the replace function uses single quotes so it is an error in the function, in this case click on edit and that’ll bring up the MySQL REPLACE command that it uses. Edit that by replacing the single quotes with double quotes.

Here’s part of the Replace amended:

SET `verse` = REPLACE(`verse`, "‘", "'")

Originally the apostrophe and left quote was surrounded by single quotes.

Click on the Edit to bring up the SQL so you can edit it if you get an error.

Hopefully that helps, here’s the MySQL UPDATE command you can use to Find and Replace whatever you need.

UPDATE myTable SET fieldName = replace(sameFieldName, 'old_text', 'new_text')

Don’t forget to use double quotes if you’re getting errors because phpMyAdmin isn’t escaping the strings!