Purpose
REGEXP_REPLACE() replaces characters in source_char that match a regular expression with characters in replace_string.
Syntax
REGEXP_REPLACE(source_char, pattern
[, replace_string
[, position
[, occurrence
[, match_param ]
]
]
]
)
Parameters
| Parameter | Description |
|---|---|
| source_char | A character expression that serves as the search value. It is usually a character column It can be of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB data type. |
| pattern | The regular expression. It is usually a text literal and can be of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type. |
| replace_string | The replacement string. It can be of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB data type. |
| position | The position where the function begins searching for a match. This optional parameter is a positive integer. The default value is 1, which means that the function begins searching at the first character of source_char. |
| occurrence | A non-negative integer that specifies the occurrence to be replaced.
|
| match_param | A character expression of the VARCHAR2 or CHAR data type. This parameter 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
The following example replaces, with *, all characters in OCEANBASE database that match (A|B){1}.
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