Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

E17122-08
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

REGEXP_REPLACE

The REGEXP_REPLACE function searches a string for a substring with a specified pattern and replaces that substring with another substring.

Return Value

VARCHAR2

Syntax

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

Parameters

source_char

The text expression for which the function searches.

pattern

The text expression for which the function searches. It is usually a text literal and can contain up to 512 bytes. The function interprets a period as a wildcard character that matches any character.

replace_string

The text that replaces pattern in source_char.

position

A positive integer that indicates the character of source_char at which the function begins the search. The default value of position is 1, which means that the function begins searching at the first character of source_char.

occurrence

A positive integer that indicates which occurrence of pattern the function should search for. The default values of occurrence is 1, meaning that the function searches for the first occurrence of pattern.

match_parameter

A text expression that lets you change the default matching behavior of the function. You can specify one or more of the values shown in the following table.

Value Specifies
c Case-sensitive matching.
i Case-insensitive matching.
m Treat the source string as multiple lines. The function interprets ^ (caret) and $ (dollar sign) as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. By default, the function treats the source string as a single line.
n A newline character is among the characters matched by a period (the wildcard character). By default, it is not.
x Ignores whitespace characters.

Example

The following statement

REGEXP_REPLACE('500   Oracle     Parkway,    Redwood  Shores, CA', '( ){2,}', ' ')

eliminates extra spaces and returns the following string

500 Oracle Parkway, Redwood Shores, CA