This online tutorial page is based on examples to be easier to follow. Function Oracle REPLACE is to replace a marked value in text string. The function will look for your input value and removes it or replaces with your replacement value if it is given. The following is Oracle Replace syntax to remove a selected value:
REPLACE(<the original text>,<your value to replace>)
To replace your input value with a new value then use the following Replace syntax:
REPLACE(<the original text>,<your value to replace>,<your new value>)
The first example is about how to use Oracle Replace function and to remove a value. In this case the main text is “surname” and the replace oracle will remove string “sur” from it. The output will become “name” as on the example below:
SELECT REPLACE('surname','sur') as replace_remove
FROM dual;
The next Oracle Select is about replacing a value with a new one.On the example below the original text has remained “surname” and we are looking for string “sur” as we did on the last example with only one difference in this time the replaceable text will not be removed but replaced. The “sur” string will be replaced with the “fore” text making the output result as “forename“.
SELECT replace('surname','sur','fore') as replace_with_your_value
FROM dual;
Based on last two examples the idea how to use OracleReplace function to remove or to replace texts should be more easy to follow.
The Replace function can be also used to count character or strings inside a text. It can be done with replacing a character with two the same one. The following example shows how to do the counting of the “@” characters.
SELECT LENGTH( REPLACE('replace@oracle.com','@','@@')) - LENGTH('replace@oracle.com') AS is_email
FROM dual;
The same example with Replace can be used to check if a string is email or not.
See Also:
Home Oracle Select