Oracle REGEXP_REPLACE Function: A Simple Guide

When you need to find and replace text in Oracle, you might think of the REPLACE function. But what if you need to find a pattern, not just a fixed string? For this, Oracle gives you the powerful REGEXP_REPLACE function.

This guide will simply explain the syntax for REGEXP_REPLACE and provide two common, practical examples.

What is the REGEXP_REPLACE Function in Oracle?

REGEXP_REPLACE is like the standard REPLACE function on steroids. It extends its functionality by letting you search a string for a regular expression (a special pattern) and replace what it finds with a new string.

This is incredibly useful for:

• Cleaning up messy data (e.g., removing extra spaces).
• Reformatting strings (e.g., changing (800)555-1212 to 800.555.1212).
• Extracting and changing parts of complex strings.

REGEXP_REPLACE Function Syntax

The syntax can look intimidating, but you often only need the first three parts.

REGEXP_REPLACE(source_char, pattern, [replace_string], [position], [occurrence], [match_param])

Let's break that down:

• source_char: The original string or column you want to search (e.g., 'Hello World' or phone_number).
• pattern: The regular expression pattern you are looking for (e.g., '[0-9]' to find a number).
• [replace_string] (Optional): The string you want to replace the match with. If you omit this, it just removes the matched pattern.
• [position] (Optional): An integer telling Oracle where to start searching. The default is 1 (the beginning of the string).
• [occurrence] (Optional): An integer telling Oracle which occurrence of the pattern to replace. The default is 0, which means replace all occurrences.
• [match_param] (Optional): A text string that changes the matching behavior. For example, 'i' makes the search case-insensitive.

Oracle REGEXP_REPLACE Function Examples

Here are two practical examples of how to use REGEXP_REPLACE.

Example 1: Removing Extra Spaces

This is a classic data-cleaning task. Imagine you have a string with multiple spaces between words, and you want to replace them with just a single space.

Here, the pattern ( ){2,} means "find a space character that repeats 2 or more times." We replace every match with a single space ' '.

Query:

SELECT 
  REGEXP_REPLACE('This   string   has    too much   space', '( ){2,}', ' ') AS "Cleaned String"
FROM DUAL;

Result:

Cleaned String
-----------------------------
This string has too much space

Example 2: Reformatting a Phone Number

This is a very common use case. You have phone numbers in one format (like 800.555.1234) and you want to change them to another (like (800) 555-1234).

We use parentheses () in the pattern to create "groups." We can then refer to these groups in the replace_string using \1, \2, and \3.

• ([[:digit:]]{3}) = Find 3 digits (Group 1)
• \. = Find a literal dot
• ([[:digit:]]{3}) = Find 3 digits (Group 2)
• \. = Find a literal dot
• ([[:digit:]]{4}) = Find 4 digits (Group 3)

Query:

SELECT 
  REGEXP_REPLACE('Call 800.555.1234 for details.',
                 '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
                 '(\1) \2-\3') AS "Formatted Phone"
FROM DUAL;

Result:

Formatted Phone
--------------------------------
Call (800) 555-1234 for details.