Description

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. This will take you hours doing it the conventional way:

open the article

make required changes

save the article

open the next article

repeat the process from step 2 for x times

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

go to DB Replacer in your Joomla! administrator

select proper content table and table column(s)

input the search and replace text

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.

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 in your database.

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

go to DB Replacer in your Joomla! administrator

select proper content table and table column(s)

input the search and replace text

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:

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.

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.

Search & Replace

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

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.

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.htmlFor 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.

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.

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 TablePro only

The default database table to select upon pageload. You can use #__ as joomla prefix.

Enter a '-' to disable.

Default ColumnsPro only

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 CountPro only

Set the maximum number of rows to return and search/replace in. If you set this too high, the page may timeout. If you need to replace someting in many rows, it is better to do replacements multiple times till there are no more results.Choose from: 50, 100, 200, 500, 1000

24-Jul-2010 : v0.1.2

+ Added display of version number in component screen
^ Changed way version and license information are displayed (now via ajax)
^ Changed way versions and licenses are checked (no more base64)

07-Jul-2010 : v0.1.1

+ Added extra WHERE field to limit search
^ Changed some layout
# Fixed issue with enters not displaying in preview
# Fixed issue with leading and trailing spaces in search/replace
# Fixed issue with special characters not being displayed correctly
# Fixed some issues with regular expressions

Documentation

Mike Hermary

Functionality

Ease of use

Big Red

I needed to strip span tags from almost 1000 K2 items. I gave DB replacer a shot at the job. Got it installed in 2 minutes, but I couldn't figure out how to use the regex feature, so I posted a question on their forum. In less than an hour I received a response and had the work completed. It does not get any better than that.

Thaddeus James

So you've just built out 50 pages of content and the client comes back with "can we move A to B and change C... on every page".

Sure.

Whilst you could write some complicated MySQL queries and do this in the database, being careful to escape all the necessary characters, it's much quicker and easier to use this component.

The ability to use WHERE clauses and regular expressions in the pro version lets you confine your searches to eg catid=10 and to find/replace items that vary from page to page (as long as they follow a predictable pattern)

I had an issue matching line endings and the developer came back within 24 hours with the solution (\s\s or \s+ in regex mode for anyone else having trouble with this)

NoNumber.nl is not affiliated with or endorsed by the Joomla Project or Open Source Matters. The Joomla name and logo are used under a limited license granted by Open Source Matters the trademark holder in the United States and other countries.