How-to: Find and replace sub text content in string fields

With SQLMerger finding and replacing text in fields is as easy as doing the same in a text document.

You can use this to e.g. replace configuration values, e.g. in an environment where you have a separate test/development and production database. You can search and replace for e.g. the http addresses in a table with configuration values, where you have a number of links to html pages that needs to be pointed to another server. Of cause you can find numerous other uses for search and replace functionality. The search and replace in SQLMerger is very flexible as you will see below.

A change script is automatically generated, so it is easy to apply the changes on other databases.

Do:

In the example we will use the "PaymentStatus" table from the built-in demo database.

1. Open the table “PaymentStatus”

This can be done by clicking the [Open Target] button

Initial contents of the "PaymentStatus" table.

2. Invoke the Search and Replace functionality.

This is done by choosing the "Replace Sub Strings (for current column)" option from the "Actions" menu.

Selecting the action in the menu.

3. Enter search and replace settings.

In the dialog that shows up, enter the text string to search for and the one to replace it with.

As you can see there are several additional options for enhancing the strength of the operation. Especially the setting for signifying where in the field the text should be is useful.

The Replace Sub Strings window, where you can specify what to replace with and detailed what to 'hit'

4. Click the [OK] button and SQLMerger makes the processing on the target data.

Afterwards the results are as shown below.

The string "wait" has been replaced and the "Upd." checkbox has been checked.

5. Click the "Script" tab.

The SQL for performing the change will be generated.

An update statement for updating the target database with the change.

6. Click the [Execute] button.

This will execute the statement.

7. Select the "Merge" tab again.

The new target data.

Back to Overview...