Oracle PL/SQL/Char Functions/REPLACE

If not found, then the replacing does not occur

 
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

Remove a letter from string with replace function

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

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

  
SQL>
SQL> SELECT REPLACE("OPS$SPORANO", "OPS$")
  2  FROM   DUAL;
REPLACE
-------
SPORANO




                                                                                                                                      Page           1

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

  
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>

REPLACE: Replace with string every time it occurs

 

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>

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

 
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

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

 
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

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

  
SQL>
SQL>
SQL> select replace( "Oracle is great!", "great", "awesome" )
  2  from dual
  3  /
REPLACE("ORACLEISG
------------------
Oracle is awesome!
1 row selected.
SQL>
SQL> --

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

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

translate vs replace

 
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>