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

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

   <source lang="sql">

SQL> SQL> SELECT REGEXP_REPLACE("Hubert Horatio Hornblower",

 2      "(.*) (.*) (.*)",
 3      "\3, \2 \1") "Reformatted Name"
 4  FROM dual ;

Reformatted Name

---

Hornblower, Horatio Hubert SQL></source>

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])

   <source lang="sql">

SQL> SELECT REGEXP_REPLACE("Mississippi", "si", "SI", 1, 0, "i")

 2  FROM dual;

REGEXP_REPL

---

MisSIsSIppi SQL></source>

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():

   <source lang="sql">

SQL> SQL> SELECT REGEXP_REPLACE("abcedfghijklumnoprstuvwxyzabcedfghijklumnoprstuvwxyzabcedfghijklumnoprstuvwxyz", "lalpha:{4}", "sound") AS result

 2  FROM dual;

RESULT

---

abcedfghijksoundprstuvwxyzabcedfghijksoundprstuvwxyzabcedfghijksoundprstuvwxyz SQL></source>

Regext_Replace

The REGEXP_REPLACE function may have these arguments:

   <source lang="sql">

REGEXP_INSTR(String to search, Pattern, [Position,

 [Occurrence, [Return-option, [Parameters]]]])</source>
   
  

The Backslash as an Escape Character

   <source lang="sql">

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></source>