Skip Headers
Oracle® OLAP Expression Syntax Reference
Release 11.2

Part Number E23381-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
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

REGEXP_REPLACE searches a string for a regular pattern and replaces it with another string. By default, the function returns source_char with every occurrence of the regular expression pattern replaced with replace_string.

Return Value

CHAR | NCHAR | VARCHAR2 | NVARCHAR2

Syntax

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

Arguments

source_char is the text expression that is searched.

pattern is the text expression to search for. It is usually a text literal and can contain up to 512 bytes. For a list of operators, refer to the Oracle Database SQL Language Reference, Appendix D, "Oracle Regular Expression Support."

replace_string is the text that replaces pattern in source_char.

position is a nonzero integer indicating the character of source_char where the function begins the search. When position is negative, then the function counts and searches backward from the end of string. The default value of position is 1, which means that the function begins searching at the first character of source_char.

occurrence is an integer indicating which occurrence of pattern the function should search for. The value of occurrence must be positive. The default values of occurrence is 1, meaning the function searches for the first occurrence of pattern.

match_parameter is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values:

Example

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

eliminates extra spaces and returns the string

500 Oracle Parkway, Redwood Shores, CA