Db2 REPLACE

Summary: in this tutorial, you will learn how to use the Db2 REPLACE() function to replace all occurrences of a string in a source string with a new string.

Db2 REPLACE() function overview

The basic syntax of the REPLACE() function is the following:

REPLACE(source_string, search_string, replace_string )
Code language: SQL (Structured Query Language) (sql)

The REPLACE() function replaces all occurrences of a search_string in a source_string with a replace_string.

In this syntax:

 source_string

Is a string or an expression that evaluate to a string which specifies the source string. The source string must not be empty.

 search_string

Is a string or an expression that evaluates to a string to be removed from the source string.

 replace_string

Is a string or an expression that evaluates to a string which specifies the replacement string.

The replace_string is optional. If you skip it, the function will do nothing.

If any argument is NULL, the REPLACE() function will return NULL.

The REPLACE() function is very useful for clean up data.

Db2 REPLACE() function examples

Let’s take some examples of using the REPLACE() function.

1) Using Db2 REPLACE() function with literal string example

This example uses the REPLACE() function to replace the string 'DB2' in the source string 'IBM DB2' with the string 'Db2':

SELECT REPLACE('IBM DB2','DB2','Db2') result FROM sysibm.sysdummy1;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

RESULT ------- IBM Db2
Code language: SQL (Structured Query Language) (sql)

2) Using Db2 REPLACE() function to modify table data example

Sometimes, you want to replace all occurrences of a string in a column with a new string. For example, you may want to replace the old link in the post content by the new link.

To do this, you use the REPLACE() function with the UPDATE statement as shown in the following command:

UPDATE table_name SET column = REPLACE(column,'search_string','replace_string') WHERE condition;
Code language: SQL (Structured Query Language) (sql)

Consider the following example.

First, create a new table named pages to store pages of a website:

CREATE TABLE pages( page_id INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY, content VARCHAR(3000) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
Code language: SQL (Structured Query Language) (sql)

Next, insert some rows into the pages table:

INSERT INTO pages(content) VALUES (' This is a test page of <a href="https://www.db2tutorial.com/test-page/">Test Page</a>'), (' This is the about page. Click here to submit your request <a href="https://www.db2tutorial.com/about/">About</a>');
Code language: SQL (Structured Query Language) (sql)

Then, query data from the pages table to verify the insert operation:

SELECT content FROM pages;
Code language: SQL (Structured Query Language) (sql)
Db2 REPLACE Function example

After that, use the REPLACE() function to replace the string http: in the content column with https:

UPDATE pages SET content = REPLACE(content,'http:','https:') WHERE content LIKE '%http:%';
Code language: SQL (Structured Query Language) (sql)

Finally, view the data of the pages table to verify the change:

SELECT content FROM pages;
Code language: SQL (Structured Query Language) (sql)
Db2 REPLACE Function - Replace http with https

In this tutorial, you have learned how to use the Db2 REPLACE() function to replace all occurrences of a search_string in a source_string with a replace_string.

Was this tutorial helpful ?