This online tutorial is based on examples to make it easier to follow. Function Oracle Replace allows replacing a value in text string. The function Replace will look for your text to be replaced and will remove or replace it with your replacement value if it is given. The following syntax of Oracle replace will remove your text from your original string:
SELECT replace(<the original text>,<your text to be replaced>) FROM <table>;
To replace your text to be replaced with a new string use the following Oracle Replace syntax:
SELECT replace(<the original text>,<your text be to replaced>,<your new text>) FROM <table>;
The first Oracle DBA example is showing how to use Replace in Oracle to remove “your text to be replaced”. In this case the main string is “surname” and Oracle Replace will remove text “sur” from it. The output will become “name” as in the example below:
SELECT replace('surname','sur') as replace_remove FROM dual;
The next Oracle Select statement is replacing “your text to be replaced” with “your new text” using the Replace function. The main string has remained “surname” and we are looking for text “sur” as we did in the last example. Only in this time the Oracle Replace function will not remove but does replace. The “sur” text will be replaced with the “fore” string making the output become “forename“.
SELECT replace('surname','sur','fore') as replace_with_your_value FROM dual;
As you see from the last two examples the Oracle Replace can be used to remove a text or to replace it with a new one.
Replacing a text can be used in a different ways and one of them is to count character in your string. This can be done with replacing an one character with two in the same text. The following Oracle DBA example shows how to count the “@” characters.
SELECT LENGTH( REPLACE('firstname.lastname@example.org','@','@@')) - LENGTH('email@example.com') AS is_email FROM dual;
In the Oracle SQL above we have main text as “firstname.lastname@example.org” and to validate the email the example counts “@” characters. In real code the validation should be more complicated, but to keep this example simple we will count only @-s. The output returned only 1 character and the output is correct as you can also see in the main text. Of course you can count in the same way other characters too and not only the @-s.