{"id":19081,"date":"2021-07-26T14:24:54","date_gmt":"2021-07-26T04:24:54","guid":{"rendered":"https:\/\/database.guide\/?p=19081"},"modified":"2021-07-27T08:47:32","modified_gmt":"2021-07-26T22:47:32","slug":"replace-function-in-oracle","status":"publish","type":"post","link":"https:\/\/database.guide\/replace-function-in-oracle\/","title":{"rendered":"REPLACE() Function in Oracle"},"content":{"rendered":"\n

In Oracle, the REPLACE()<\/code> function allows you to replace a part of a string with another string.<\/p>\n\n\n\n

The function accepts three arguments: the string, the substring to replace, and the replacement string (to replace the substring with). <\/p>\n\n\n\n

You can omit the replacement string, in which case, the function will remove all instances of the substring.<\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n

The syntax goes like this:<\/p>\n\n\n\n

REPLACE(char, search_string\n        [, replacement_string ]\n       )<\/code><\/pre>\n\n\n\n
Where char<\/code> is the string, search_string<\/code> is the substring to replace, and replacement_string<\/code> is the string to replace that substring with.<\/p>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s a basic example:<\/p>\n\n\n\n
SELECT \n    REPLACE('Barrier Island', 'Island', 'Reef')\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Barrier Reef<\/pre>\n\n\n\n
In this case we replaced the substring Island<\/code> with Reef<\/code>.<\/p>\n\n\n\n
Multiple Matches<\/h2>\n\n\n\n
If the string to be replaced occurs multiple times within the string, all occurrences are replaced:<\/p>\n\n\n\n
SELECT \n    REPLACE('Black dogs and white dogs', 'dog', 'cat')\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Black cats and white cats<\/pre>\n\n\n\n
No Match<\/h2>\n\n\n\n
If the substring doesn’t occur in the string, REPLACE()<\/code> returns the string unchanged:<\/p>\n\n\n\n
SELECT \n    REPLACE('Barrier Island', 'to entry', 'Reef')\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Barrier Island<\/pre>\n\n\n\n
Remove a Substring<\/h2>\n\n\n\n
Omitting the third argument removes the substring from the string:<\/p>\n\n\n\n
SELECT \n    REPLACE('Black dogs and white dogs', 'dogs')\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Black  and white <\/pre>\n\n\n\n
If you look closely, you’ll notice that the spaces still remain, as we didn’t specify any spaces in the substring.<\/p>\n\n\n\n
Here it is again with the space included:<\/p>\n\n\n\n
SELECT \n    REPLACE('Black dogs and white dogs', ' dogs')\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Black and white<\/pre>\n\n\n\n
Case Sensitivity<\/h2>\n\n\n\n
The REPLACE()<\/code> function performs a case-sensitive match:<\/p>\n\n\n\n
SELECT \n    REPLACE('Barrier Island', 'island', 'reef')\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Barrier Island<\/pre>\n\n\n\n
In this example, the case didn’t match, and so the original string was returned unchanged.<\/p>\n\n\n\n
Empty Strings<\/h2>\n\n\n\n
Here’s what happens when an empty string is passed for each given argument:<\/p>\n\n\n\n
SELECT \n    REPLACE('', 'dog', 'cat') AS \"1\",\n    REPLACE('Black dog', '', 'cat') AS \"2\",\n    REPLACE('Black dog', 'dog', '') AS \"3\"\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
   1            2         3 \n____ ____________ _________ \n     Black dog    Black    <\/pre>\n\n\n\n
So in this case:<\/p>\n\n\n\n