How To Use Oracle REPLACE
  Home
 

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;

online tech support computer help computer technician computer problems oracle database oracle sql database programming database oracle retirement planning retirement oracle replace oracle replace in oracle

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;

online tech support computer help computer technician computer problems oracle database oracle sql database programming database oracle retirement planning retirement oracle replace oracle replace in oracle

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;

online tech support computer help computer technician computer problems oracle database oracle sql database programming database oracle retirement planning retirement oracle replace oracle replace in oracle

The same example with Replace can be used to check if a string is email or not.


See Also:
Home Oracle Select

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*