{"id":821,"date":"2019-06-10T00:32:29","date_gmt":"2019-06-10T08:32:29","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=821"},"modified":"2020-04-11T06:57:59","modified_gmt":"2020-04-11T14:57:59","slug":"db2-replace","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-string-functions\/db2-replace\/","title":{"rendered":"Db2 REPLACE"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 REPLACE()<\/code> function to replace all occurrences of a string in a source string with a new string.<\/p>\n\n\n\n

Db2 REPLACE() function overview<\/h2>\n\n\n\n

The basic syntax of the REPLACE()<\/code> function is the following:<\/p>\n\n\n

REPLACE<\/span>(source_string, search_string, replace_string )\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The REPLACE()<\/code> function replaces all occurrences of a search_string<\/code> in a source_string<\/code> with a replace_string<\/code>.<\/p>\n\n\n\n
In this syntax:<\/p>\n\n\n\n
 source_string<\/h3>\n\n\n\n
Is a string or an expression that evaluate to a string which specifies the source string. The source string must not be empty.<\/p>\n\n\n\n
 search_string<\/h3>\n\n\n\n
Is a string or an expression that evaluates to a string to be removed from the source string.<\/p>\n\n\n\n
 replace_string<\/h3>\n\n\n\n
Is a string or an expression that evaluates to a string which specifies the replacement string.<\/p>\n\n\n\n
The replace_string<\/code> is optional. If you skip it, the function will do nothing.<\/p>\n\n\n\n
If any argument is NULL, the REPLACE()<\/code> function will return NULL.<\/p>\n\n\n\n
The REPLACE()<\/code> function is very useful for clean up data.<\/p>\n\n\n\n
Db2 REPLACE() function examples<\/h2>\n\n\n\n
Let’s take some examples of using the REPLACE()<\/code> function.<\/p>\n\n\n\n
1) Using Db2 REPLACE() function with literal string example<\/h3>\n\n\n\n
This example uses the REPLACE()<\/code> function to replace the string 'DB2'<\/code> in the source string 'IBM DB2'<\/code> with the string 'Db2'<\/code>:<\/p>\n\n\n
SELECT<\/span> \n    REPLACE<\/span>('IBM DB2'<\/span>,'DB2'<\/span>,'Db2'<\/span>) result<\/span>\nFROM<\/span> \n    sysibm.sysdummy1;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
RESULT  \n------- <\/span>\nIBM Db2 \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using Db2 REPLACE() function to modify table data example<\/h3>\n\n\n\n
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.<\/p>\n\n\n\n
To do this, you use the REPLACE()<\/code> function with the UPDATE<\/a><\/code> statement as shown in the following command:<\/p>\n\n\n
UPDATE<\/span> \n    table_name\nSET<\/span> \n    column<\/span> = REPLACE<\/span>(column<\/span>,'search_string'<\/span>,'replace_string'<\/span>)\nWHERE<\/span> \n    condition;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Consider the following example.<\/p>\n\n\n\n
CREATE<\/span> TABLE<\/span> pages(\n    page_id INT<\/span> NOT<\/span> NULL<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n    content<\/span> VARCHAR<\/span>(3000<\/span>) NOT<\/span> NULL<\/span>,\n    created_at TIMESTAMP<\/span> DEFAULT<\/span> CURRENT_TIMESTAMP<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> pages(content<\/span>)\nVALUES<\/span>\n(' This is a test page of <a href=\"https:\/\/db2tutorial.com\/test-page\/\">Test Page<\/a>'<\/span>),\n(' This is the about page. Click here to submit your request <a href=\"https:\/\/db2tutorial.com\/about\/\">About<\/a>'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, query data from the pages<\/code> table to verify the insert operation:<\/p>\n\n\n
SELECT<\/span> \n    content<\/span>\nFROM<\/span> \n    pages;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"Db2<\/figure>\n\n\n\n
After that, use the REPLACE()<\/code> function to replace the string http:<\/code> in the content column with https:<\/code><\/p>\n\n\n
UPDATE<\/span> \n    pages \nSET<\/span> \n    content<\/span> = REPLACE<\/span>(content<\/span>,'http:'<\/span>,'https:'<\/span>)\nWHERE<\/span> \n    content<\/span> LIKE<\/span> '%http:%'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, view the data of the pages<\/code> table to verify the change:<\/p>\n\n\n
SELECT<\/span> \n    content<\/span>\nFROM<\/span> \n    pages;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"Db2<\/figure>\n\n\n\n
In this tutorial, you have learned how to use the Db2 REPLACE()<\/code> function to replace all occurrences of a search_string<\/code> in a source_string<\/code> with a replace_string<\/code>.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Next, insert<\/a> some rows into the pages<\/code> table:<\/p>\n\n\n
First, create a new table<\/a> named pages<\/code> to store pages of a website:<\/p>\n\n\n