Oracle PL/SQL/Char Functions/REPLACE

If not found, then the replacing does not occur

   <source lang="sql">

SQL> SQL> SQL> --If not found, then the replacing does not occur SQL> SQL> SELECT REPLACE ("This is a test","glurg"," may be ") FROM dual; REPLACE("THISI

---

This is a test

 </source>
   
  

Remove a letter from string with replace function

   <source lang="sql">
 

SQL> SQL> SQL> select REPLACE ("ABCDA", "A","") remove_A from dual; REMOVE_A

---

---

BCD SQL> SQL>

 </source>
   
  

REPLACE("OPS$SPORANO", "OPS$")

   <source lang="sql">
 

SQL> SQL> SELECT REPLACE("OPS$SPORANO", "OPS$")

 2  FROM   DUAL;

REPLACE

---

SPORANO

                                                                                                                                     Page           1
  
 </source>
   
  

REPLACE() replaces every occurrence of the string specified as the search_string with the replacement_string.

   <source lang="sql">
 

SQL> Syntax: REPLACE(<string_expression>, <search_string> [, <replacement_string>]) SQL> SQL> SQL> SELECT

 2     REPLACE ("ABCDA", "A","*")  replace_A,
 3     REPLACE ("ABCDA", "A")      remove_A,
 4     REPLACE ("ABCDA",NULL)      null_search
 5  FROM dual;

REPLA REM NULL_

---

--- -----

• BCD* BCD ABCDA

SQL> SQL>

 </source>
   
  

REPLACE: Replace with string every time it occurs

   <source lang="sql">

SQL> SQL> -- Replace with string every time it occurs. SQL> SQL> SELECT REPLACE ("This is a test"," is "," may be ") FROM dual; REPLACE("THISISATE

---

This may be a test SQL>

 </source>
   
  

Replace: returns a string in which every occurrence of the pattern_to_find has been replaced with pattern_to_replace_by

   <source lang="sql">

SQL> SQL> -- Replace: returns a string in which every occurrence of the pattern_to_find has been replaced with pattern_to_replace_by. SQL> SQL> --The general format for this function is: SQL> SQL> --REPLACE(source_string, pattern_to_find, pattern_to_replace_by) SQL> SQL> SELECT REPLACE("Mississippi", "pi", "PI") FROM dual; REPLACE("MI

---

MississipPI

 </source>
   
  

REPLACE("This is a test","t","XYZ")

   <source lang="sql">

SQL> -- Regext_Replace SQL> -- An example of the REPLACE function SQL> SQL> SELECT REPLACE("This is a test","t","XYZ") FROM dual; REPLACE("THISISATE

---

This is a XYZesXYZ

 </source>
   
  

select replace( "Oracle is great!", "great", "awesome" )

   <source lang="sql">
 

SQL> SQL> SQL> select replace( "Oracle is great!", "great", "awesome" )

 2  from dual
 3  /

REPLACE("ORACLEISG

---

Oracle is awesome! 1 row selected. SQL> SQL> --

 </source>
   
  

Syntax: REPLACE (<string_expression>,<search_string> [,<replacement_string>])

   <source lang="sql">
 

SQL> SQL> SQL> SELECT REPLACE ("ABCDA", "A","*") replace_A from dual; REPLACE_A

---

---

• BCD*

SQL>

 </source>
   
  

translate vs replace

   <source lang="sql">

SQL> SQL> select translate("beer bucket","beer","milk") as translate

 2  ,      replace  ("beer bucket","beer","milk") as replace_1
 3  ,      replace  ("beer bucket","beer")        as replace_2
 4  from   dual;

TRANSLATE REPLACE_1 REPLACE

---

----------- -------

miik muckit milk bucket bucket SQL>

 </source>