{"id":327,"date":"2017-07-30T16:29:51","date_gmt":"2017-07-30T09:29:51","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=327"},"modified":"2025-04-24T06:27:59","modified_gmt":"2025-04-24T13:27:59","slug":"oracle-replace","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/oracle-string-functions\/oracle-replace\/","title":{"rendered":"Oracle REPLACE Function"},"content":{"rendered":"\n

The Oracle REPLACE()<\/code> function replaces all occurrences of a specified substring in a string with another.<\/p>\n\n\n\n

Syntax #<\/a><\/h2>\n\n\n\n

Here’s the syntax of the Oracle REPLACE()<\/code> function:<\/p>\n\n\n

REPLACE<\/span>(string_expression, string_pattern [,string_replacement])<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Arguments #<\/a><\/h2>\n\n\n\n
The Oracle REPLACE()<\/code> function accepts three arguments:<\/p>\n\n\n\n
1) string_expression<\/code><\/p>\n\n\n\n
is a string (or an expression that evaluates to a string) to be searched.<\/p>\n\n\n\n
2) string_pattern<\/code><\/p>\n\n\n\n
is a substring to be replaced.<\/p>\n\n\n\n
3) string_replacement<\/code><\/p>\n\n\n\n
is the replacement string.<\/p>\n\n\n\n
Return Value #<\/a><\/h2>\n\n\n\n
The REPLACE()<\/code> function returns a string with every occurrence of the string_pattern<\/code> replaced with the string_replacement<\/code>.<\/p>\n\n\n\n
If you omit the string_replacement<\/code>, the REPLACE()<\/code> function removes all occurrences of the string_pattern<\/code> in the string_expression<\/code>.<\/p>\n\n\n\n
In case the string_pattern<\/code> is null or empty, the REPLACE()<\/code> function returns the string_expression<\/code>.<\/p>\n\n\n\n
Examples #<\/a><\/h2>\n\n\n\n
The following statement replaces is<\/code> with 'IS'<\/code> in the string ‘This is a test'<\/code>:<\/p>\n\n\n
SELECT<\/span>\n  REPLACE<\/span> ('This is a test'<\/span>, 'is'<\/span>, 'IS'<\/span>)\nFROM<\/span>\n  dual;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
'thIS IS a test'<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
We often use the REPLACE()<\/code><\/code> function to modify the data in tables. <\/p>\n\n\n\n
First, create the articles<\/code> table with the following structure:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> articles (\n  article_id NUMBER<\/span> GENERATED<\/span> BY<\/span> DEFAULT<\/span> AS<\/span> IDENTITY<\/span>,\n  title VARCHAR2<\/span> (255<\/span>),\n  article_body VARCHAR2<\/span> (4000<\/span>),\n  PRIMARY KEY<\/span> (article_id)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Next, insert sample data<\/a> into the articles<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  articles (title, article_body)\nVALUES<\/span>\n  (\n    'Sample article'<\/span>,\n    'This is a <strong>sample<\/strong> article'<\/span>\n  );\n\nINSERT<\/span> INTO<\/span>\n  articles (title, article_body)\nVALUES<\/span>\n  (\n    'Another article'<\/span>,\n    'Another excellent <strong>sample<\/strong> article'<\/span>\n  );<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, query data<\/a> from the articles<\/code> table:<\/p>\n\n\n
SELECT<\/span>\n  article_id,\n  title,\n  article_body\nFROM<\/span>\n  articles;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"Oracle<\/figure>\n\n\n\n
After that, suppose you want to replace all <strong><\/code> tags with <b><\/code> tags in the article_body<\/code> column. Here are the queries to do so:<\/p>\n\n\n
UPDATE<\/span> articles\nSET<\/span>\n  article_body = REPLACE<\/span> (article_body, '<strong>'<\/span>, '<b>'<\/span>);\n\nUPDATE<\/span> articles\nSET<\/span>\n  article_body = REPLACE<\/span> (article_body, '<\/strong>'<\/span>, '<\/b>'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
These queries used the REPLACE()<\/code> function to replace <strong><\/code> with <b><\/code> and <\/strong><\/code> with <\/b><\/code>.<\/p>\n\n\n\n
Finally, query data from the articles<\/code> table to verify the replacements:<\/p>\n\n\n
SELECT<\/span>\n  article_id,\n  title,\n  article_body\nFROM<\/span>\n  articles;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"Oracle<\/figure>\n\n\n\n
The output shows that the statement replaced the <strong><\/code> tags with the b<\/code> tags.<\/p>\n\n\n\n
Remarks #<\/a><\/h2>\n\n\n\n
Oracle provides you with the TRANSLATE()<\/code> function that has similar functionality to the REPLACE()<\/code> function.<\/p>\n\n\n\n
However, the TRANSLATE()<\/code> function provides single-character, one-to-one substitution, while the REPLACE()<\/code> function allows you to substitute one string for another.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the Oracle REPLACE()<\/code> function to replace all occurrences of a substring in a string with another.<\/li>\n<\/ul>\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
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Let’s create a new table<\/a> named articles<\/code> for the demonstration.<\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n