Tutorial:

Introduction

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

To replace a certain string in your database table, you just need to:

  1. go to DB Replacer in your Joomla! administrator
  2. select proper content table and 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 know what you are doing before actually replacing anything.

Using DB Replacer

Using DB Replacer is quite straightforward. Open DB Replacer from the component menu in the Joomla! administrator. You will be presented with the following component interface:

DB Replacer

Tables


In the Tables select box you will see the list of all database tables from which you can choose one table in which you want to make the changes.

DB Replacer - Tables

Columns


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 Control/Command key pressed when selecting.

DB Replacer - Columns

Search & Replace

Enter the existing text to be replaced in the Search text box.

DB Replacer - Search & Replace

Once you enter some text in this field, the Preview table below will only show the rows which contain your search string.

Enter the new replacement text in the Replace text box.

The Preview table will show you a preview of the replacement. The search string will be marked red and the replacement will be marked green.

DB Replacer - Preview

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

You can search and replace in up to 1000 rows at one time (up to 50 in FREE version).
WE RECOMMEND not setting 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.

Case sensitivity

If you need to make changes where case sensitivity is required, you can check “Case sensitive” checkbox. Now the replacements will only be done where the search matches exactly.

For instance, ‘Joomla’ will not match ‘joomla’ or ‘JOOMLA’.

Regular Expressions Pro only

The PRO version of DB Replacer also supports Regular Expressions searches and replacements.

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.

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

Where clause Pro only

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

DB Replacer - Where

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 records that have an id between 100 and 200, you can enter:

id > 100 AND id < 200

Options

In the toolbar, on the right, you will see the 'Options' button.

DB Replacer - Options

When you click on that, a modal window will open with a couple of options for DB Replacer.

Here is an overview of the available options. All descriptions are available in the tooltips of the 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.

Permissions

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

FAQs:

Is it possible to multi select tables in one database?

No, not possible with DB Replacer. You can only search and replace in 1 table at a time.

To do replaces in multiple tables you will have to repeat the replace process for each table.

Another method is to execute your own custom sql command on your database directly, via phpMyAdmin for instance.

How can I replace empty or NULL values

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

How can I replace the entire value

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

Where can I find more information on Regular Expressions?

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

Free vs Pro:

Search and Replace in any database table
Live preview of what will be replaced
Case sensitive::Ability to search case sensitive or insensitive
Replace in up to 50 rows at a time

Regular Expression::Ability to use Regular Expressions to search and replace
Limit search by WHERE::Use an extra WHERE field to limit the rows in which to search and replace
Replace in up to 1000 rows at a time

DB Replacer enables you to search and replace in any table in your database.

It even supports searching with case sensitivity and using 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 will take you hours doing it the conventional way:

  1. open the article manager
  2. search for your article
  3. open the article
  4. make required changes
  5. save the article
  6. search for the next article
  7. open the that article
  8. make required changes
  9. save the article
  10. repeat the step 6 - 9 another 3 zillion times

With DB Replacer this takes a few seconds. All you need to do is to

  1. go to DB Replacer in your Joomla! administrator
  2. select proper content table and 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 know what you are doing before actually replacing anything.

WARNING: DB Replacer replaces stuff right in your database in the places you tell it to. This can - if you replace the wrong stuff - break your site. So make a backup, be careful and only use this 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.