All Products
Search
Document Center

REGEXP_REPLACE

Last Updated: Jun 18, 2021

The REGEXP_REPLACE function replaces regular expressions.

Syntax

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

Parameters

Parameter

Description

source_char

The character expression that is used as a search value. It is generally a character column and its data type can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB.

pattern

The regular expression. It is generally a text literal and its data type can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2.

replace_string

The replacement character. The type can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB.

position

The positive integer type. It indicates the ordinal number of the character from which ApsaraDB for OceanBase starts to search the character of source_char. The default value is 1 and indicates that ApsaraDB for OceanBase starts to search source_char from the first character.

occurrence

The non-negative integer. It indicates that the replacement operation occurs. If you specify this parameter as 0, ApsaraDB for OceanBase replaces all the matching items. If you specify this parameter as a positive integer n, ApsaraDB for OceanBase replaces the matching items that occur for the nth time. By default, all the matching items are replaced. If you specify this parameter as 0, all the matching items are also replaced.

match_param

The character expression of the VARCHAR2 or CHAR data type. It allows you to change the default matching behavior of the function.

i indicates that the match is not case-sensitive. c indicates that the match is case-sensitive. n represents periods (.). The period (.) indicates that line feeds are not matched. m indicates the multi-line mode. x indicates that space characters are ignored. By default, space characters match with each other.

Return type

The returned result has the same data type as source_char.

Examples

The following example checks the string to search for two or more spaces. ApsaraDB for OceanBase replaces each occurrence of two or more spaces with one space. Execute the following statement:

SELECT REGEXP_REPLACE('500   OceanBase     Parkway,    Redwood  Shores, CA', '( ){2,}', ' ') "REGEXP_REPLACE"
FROM DUAL;

The following query result is returned:

REGEXP_REPLACE                        
--------------------------------------
500 OceanBase Parkway, Redwood Shores, CA