Oracle PL/SQL Tutorial/Regular Expressions Functions/REGEXP REPLACE

Convert a name in the form "first middle last" into the "last, middle first" format

SQL>
SQL> SELECT REGEXP_REPLACE("Hubert Horatio Hornblower",
  2      "(.*) (.*) (.*)",
  3      "\3, \2 \1") "Reformatted Name"
  4  FROM dual ;
Reformatted Name
--------------------------
Hornblower, Horatio Hubert
SQL>

Regexp_Replace returns the source_string with every occurrence of the pattern_to_find replaced with the replace_string

The simplest format for this function is:

REGEXP_REPLACE (source_string, pattern_to_find, pattern_to_replace_by)

The general format for the REGEXP_REPLACE function with all the options is:

REGEXP_REPLACE (source_string, pattern_to_find,[pattern_to_replace_by, position, occurrence,match_parameter])

SQL> SELECT REGEXP_REPLACE("Mississippi", "si", "SI", 1, 0, "i")
  2  FROM dual;
REGEXP_REPL
-----------
MisSIsSIppi
SQL>

REGEXP_REPLACE(x, pattern [, replace_string [, start [, occurrence [, match_option]]]]) searches x for pattern and replace it with replace_string

The following example replaces the substring that matches the regular expression l alpha:{4} with the string sound using REGEXP_REPLACE():

SQL>
SQL> SELECT REGEXP_REPLACE("abcedfghijklumnoprstuvwxyzabcedfghijklumnoprstuvwxyzabcedfghijklumnoprstuvwxyz", "l[[:alpha:]]{4}", "sound") AS result
  2  FROM dual;
RESULT
------------------------------------------------------------------------------
abcedfghijksoundprstuvwxyzabcedfghijksoundprstuvwxyzabcedfghijksoundprstuvwxyz
SQL>

Regext_Replace

The REGEXP_REPLACE function may have these arguments:

REGEXP_INSTR(String to search, Pattern, [Position,
  [Occurrence, [Return-option, [Parameters]]]])

The Backslash as an Escape Character

SQL>
SQL> SELECT REGEXP_REPLACE("$1,234.56","$"," ") FROM dual;
REGEXP_REP
----------
$1,234.56
SQL>
SQL> SELECT REGEXP_REPLACE("$1,234.56","\$"," ") FROM dual;
REGEXP_RE
---------
 1,234.56
SQL>