Oracle REGEXP_REPLACE Guide: Syntax & Examples

In Oracle, you can utilize the REGEXP_REPLACE function for regex replacement.

Syntax:
REGEXP_REPLACE(source_string, pattern, replacement [, start_position [, occurrence [, match_parameter ]]])

Explanation of Parameters:

1. source string: the string to be replaced.
2. Pattern: Regular expression pattern used to match content to be replaced.
3. replaced string: the string that has been substituted.
4. Optional: specify the starting position in the source string for the replacement, with the default value being 1.
5. occurrence: specifies the number of times to replace the matching item, with the default being to replace all matching items.
6. Optional match parameter can define some matching options for regular expressions, such as case sensitivity.

Original: 我最好的朋友是我从小就认识的。
Paraphrased: My best friend is someone I have known since I was a child.

1. Replace all the letters in the string with “”: SELECT REGEXP_REPLACE(‘Hello World’, ‘[a-zA-Z]’, ”) FROM dual; Result: **** *****
2. Replace the numbers in the string with an empty string:
   SELECT REGEXP_REPLACE(‘abc123def456’, ‘[0-9]’) FROM dual;
   Result: abcdef
3. Replace the first occurrence of a specified string in a given string:
   SELECT REGEXP_REPLACE(‘Hello World’, ‘o’, ”) FROM dual;
   Result: Hell World
4. Replace the second occurrence of a specified string in the string:
   SELECT REGEXP_REPLACE(‘Hello World’, ‘o’, ”, 1, 2) FROM dual;
   Output: Hello Wrld