How to replace specific characters in Oracle?

In Oracle, the SUBSTR function can be used to replace characters at a specific position.

For example, to replace the comma with an exclamation mark in the string “Hello, World!”, you can use the following statement:

SELECT SUBSTR('Hello, World!', 1, INSTR('Hello, World!', ',') - 1) || '!' || SUBSTR('Hello, World!', INSTR('Hello, World!', ',') + 1)
FROM DUAL;

This statement will return the replaced string “Hello! World!”.

The SUBSTR function is used to split the string, while the INSTR function is used to find the position of a specified character. By splitting the string into two parts and inserting a new character in between, replacement is achieved.