DB Replacer

Search and replace in your Joomla! database

  • Last updated: 20 Feb 2020
  • Version: 6.3.7
  • Type: Component
  • Joomla rating: 100%

Tutorial for DB Replacer

There is a Free and a Pro version of DB Replacer.
The parts in this tutorial that only concern the Pro version will be marked with: Pro only

Introduction

DB Replacer is Joomla! administrator component that enables you to search and replace in any table of your Joomla database.

It even supports searching with case sensitivity and using the powerful Regular Expressions.

DB Replacer will save you hours of manual labor!

Sometimes you need to replace something in all your articles (or other content). This would take you hours doing it the conventional way, manually opening and changing each article.

With DB Replacer, the process takes just a few seconds. To replace a certain string in your database table, you simply need to:

  1. Go to DB Replacer in your Joomla! administrator
  2. Select a database table and the desired table column(s)
  3. Input the search and replace text
  4. Click on ‘Replace’

DB Replacer gives you a preview of what is going to be replaced, so you can see what will happen before actually replacing anything.


Note: Differently from ReReplacer (which does replacements on-the-fly, leaving the original text unchanged), with DB Replacer the replacements are done permanently in the database, so make sure you know what you're doing.

Getting Started

Once DB Replacer is installed, you will be able to access it via the Regular Labs - DB Replacer menu link under Components.

Using DB Replacer is quite straightforward. You will be presented with the following component interface:

DB Replacer - Overview

Table & Columns

In the Tables select box, you will see the list of all the database tables of your Joomla installation.

Simply choose the table in which you want to make the changes. You can only search and replace in one table at a time.

By default, the Joomla content table is selected. The default selection can be changed in the DB Replacer Configuration Settings.

After selecting the table, the Columns select box will be populated.

You can choose one or more columns here. To select multiple columns, keep the CTRL (or Command for Mac) key pressed when selecting them.

DB Replacer - Table & Columns

Search & Replace

Enter in the Search text box the existing text that you want to replace. You can replace the searched text with something else, or simply remove it (by replacing it with nothing).

DB Replacer - Search & Replace

Then enter the new replacement text in the Replace text box, and click the Search Button.

The Preview window at the bottom of the page will now show you a preview of the replacement, showing the rows which contain your search string. The original search string will be marked red and the new replacement will be marked green.

DB Replacer - Preview

If you are satisfied with the Preview result, you can simply click on the Replace button to apply the changes.

Note: You can search and replace in up to 1000 rows at one time (up to 50 in FREE version). WE RECOMMEND to not set the default row count limit too high (around 100). Replacing in too many rows at once can make your page time out, depending on server and database settings.

Special search words

DB Replacer comes with a couple of special syntaxes you can use in the search area:

You can match the entire value of a field by placing a single asterisk * in the search area.

You can match empty, NULL or 0000-00-00 date values by simply placing NULL in the search area.

Case Sensitivity

Unlike direct MySQL replacements, DB Replacer has the advantage of making case-insensitive replacements. The replacements made by DB Replacer are case-insensitive by default.

This means that ‘Joomla’ would also match ‘joomla’and ‘JOOMLA’.

However, if you need to make changes where case sensitivity is required, you can check "Case sensitive" checkbox. If you switch on that setting, replacements will only be done where the search matches exactly.

So in this case, ‘Joomla’ would NOT match ‘joomla’ or ‘JOOMLA’.

Regular Expressions Pro only

The PRO version of DB Replacer also supports the use of Regular Expressions to search and replace. 

This gives you great power to search for dynamic values and use (parts of) the searched text in the replacement.

For example, if you want to make all prices with syntax $123.00 bold, you can search for:

(\$[0-9]+\.[0-9][0-9])

And replace with:

<strong>\1</strong>

This will make $1.00 and $999.00 bold, but ignore $1 and $999.

When the Regular Expressions option is checked, you will also be able to search in UTF-8 mode. This can help you when using special characters or certain character sets.

More on Regular Expressions

For help and documentation on regular expressions, ReReplacer comes with a Regular Expressions Cheat Sheet.

For more info on Regular Expressions: http://www.regular-expressions.info/reference.html
For testing them: http://gskinner.com/RegExr, http://regexpal.com, http://www.phpliveregex.com

Where clause Pro only

With the PRO version of DB Replacer you can limit the search via a custom WHERE clause.

DB Replacer - Where Clause

The WHERE clause is used to extract only those records that fulfill specific criteria.

This field follows the default MySQL WHERE syntax. For instance, if you only want to search and replace in articles inside category with ID 5, you can do:

catid = 5

To search and replace in records that have an ID between 100 and 200, you can enter:

id > 100 AND id < 200

Or with string values:

name = "Mike" OR username LIKE "%mike%"

Handle with care!

With DB Replacer, the replacements are done permanently in the database in the places you tell it to. This means that if you replace the wrong stuff - you can potentially break your site.

So make backups, be careful, and only use DB Replacer if you know what you are doing.

We cannot accept any liability for the result, and we make no expressed or implied guarantee of safe use.

Settings

From the DB Replacer component view, in the toolbar on the right, you will see the "Options" button.

DB Replacer - Options

When you click on that, you will be directed to the Global Configuration options for DB Replacer.

Here is an overview of the available options. All descriptions are available in the tooltips of the options.

Options

Ignore Tables A comma separated list of table prefixes/names to ignore. Any table that starts with this will not be shown in the results.
Default Table The default database table to select upon pageload. You can use #__ as joomla prefix.
Enter a '-' to disable.
Default Columns A comma separated list of default database columns to select when the default table is selected. These columns must therefore be available in the default table.
Enter a '-' to disable.
Maximum Row Count Maximum Row Count
Options: 50, 100, 200, 500, 1000
Show 2nd Warning Select to show a second 'Are you sure' alert before replacing.
Show Update Notification If selected, an update notification will be shown in the main component view when there is a new version for this extension.
Show Copyright If selected, extra copyright info will be displayed in the admin views. Regular Labs extensions never show copyright info or backlinks on the frontend.

User Actions Log

Log User Actions Select to store User Actions. These actions will be visible in the User Actions Log module.

Permissions

The Joomla permission settings control access to the different actions for each user group. For more information on how these permissions work, see the Joomla docs: Access Control List Tutorial: Component Options & Permission

Installation

You can either install DB Replacer by using the core extension manager available in the Joomla! Administrator Control Panel, or by using the powerful Regular Labs Extension Manager.

Note: When updating DB Replacer, you do not need to uninstall it first. The package will update all the files automatically.

Keep in mind that when you update to a major new version (or uninstall first), you might lose some configuration settings.

Regular Labs Extension Manager

It is very easy to install/update any Regular Labs Extension using the Regular Labs Extension Manager.

Please see the Tutorial for the Regular Labs Extension Manager for more detailed information...

Joomla! Extension Manager

To install via the Joomla! Extension Manager, just follow these steps:

  1. Log into your Joomla administrator;
  2. In the menu, choose: Extensions >> Extensions;
  3. Choose the tab: Install from Web (or enable it if you haven't done so yet);
  4. Select the search field and enter DB Replacer and hit enter;
  5. Click on the DB Replacer listing;
  6. Click on Install;
  7. Click on Install to confirm.
Joomla! Installer - Install from Web

Or if you need to install the Pro version or cannot install via the 'Install via Web' method for some reason:

  1. Download the extension install file (.zip);
  2. Log into your Joomla administrator;
  3. In the menu, choose: Extensions >> Extensions;
  4. Choose the tab: Upload Package File;
  5. Click on the Choose File button and select the extension zip;
  6. Click on Upload & Install.
Joomla! Installer - Upload Package File

If you have problems installing DB Replacer, please try the manual installation process as described here: docs.joomla.org/Installing_an_extension