How To Use REPLACE In Oracle

How To Use REPLACE In Oracle

July 07, 2020
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

dba oracle dba database management database management system oracle performance tuning oracle replace oracle replace in oracle online courses online personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

dba oracle dba database management database management system oracle performance tuning oracle replace oracle replace in oracle online courses online personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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('replace@oracle.com','@','@@')) - LENGTH('replace@oracle.com') 
        AS is_email
  FROM dual;

dba oracle dba database management database management system oracle performance tuning oracle replace oracle replace in oracle online courses online personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

In the Oracle SQL above we have main text as “replace@oracle.com” 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.

See Also:
Oracle Select Home

Leave a Reply

Your email address will not be published. Required fields are marked *

*