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://db2tutorial.com/test-page/">Test Page</a>'),
(' This is the about page. Click here to submit your request <a href="https://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)

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)

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.