Oracle Regular Expressions Pocket Reference

Name

REGEXP_REPLACE — Replaces text matching a pattern

Synopsis

REGEXP_REPLACE searches a string for substrings matching a regular expression, and replaces each substring with text that you specify. Your replacement text may contain backreferences to subexpressions in the regular expression. The new string, with all replacements made, is returned as the function’s result.

REGEXP_REPLACE returns either a VARCHAR2 or a CLOB, depending on the input type. The return value’s character set will match that of the source string.

Syntax

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

All parameters after the first two are optional. However, to specify any one optional parameter, you must specify all preceding parameters. Thus, if you want to specify match_parameter, you must specify all parameters.

Parameters

source_string

The string containing the substrings that you want to replace.

pattern

A regular expression describing the text pattern of the substrings you want to replace. Maximum length is 512 bytes.

replace_string

The replacement text. Each occurrence of pattern in source_string is replaced by replace_string. See Section 1.6.8 later in this section for important information on using regular expression backreferences in the replacement text.

Maximum length is 32,767 bytes. Any replacement text value larger than 32,767 bytes will be truncated to that length.

Note

If you’re using multibyte characters, truncation might result in less than 32,767 ...