Purpose
This function replaces characters in the string source_char that match a regular expression with characters in the replace_string string.
Syntax
REGEXP_REPLACE(source_char, pattern
[, replace_string
[, position
[, occurrence
[, match_param ]
]
]
]
)
Parameters
Parameter |
Description |
|---|---|
| source_char | A character expression used as the search value. It is typically a character column. The data type can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB. |
| pattern | The regular expression, which is typically a text literal. The data type can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2. |
| replace_string | The characters to be replaced. It can be of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB data type. |
| position | The starting position for the regular expression match. It is a positive integer and is optional. The default value is 1, indicating that the search starts from the first character of source_char. |
| occurrence | The occurrence number of the match to be replaced. It is a non-negative integer.
|
| match_param | A character expression of the VARCHAR2 or CHAR data type that allows you to change the default matching behavior of the function.
|
Return type
The return type is the same as the data type of source_char.
Examples
Replace all characters that match the pattern (A|B){1} in the string OCEANBASE database with *.
obclient> SELECT REGEXP_REPLACE('OCEANBASE database', '(A|B){1}', '*',1,0) AS "REGEXP_REPLACE" FROM DUAL;
+--------------------+
| REGEXP_REPLACE |
+--------------------+
| OCE*N**SE database |
+--------------------+
1 row in set