REGEXP_REPLACE oracle 10g feature

Learn how to replace a string with another string using REGEXP_REPLACE with example queries.

REGEXP_REPLACE -replace all occurrences of a substring that match a regular expression with another substring. It is similar to the REPLACE function, except it uses a regular expression to select the substring to be replaced.

REGEXP_REPLACE(<Source_string>, <Search_pattern>, <Replacement_string>, <Start_position>, <Match_occurrence>, <Match_modifiers>)

Source_string: The string to be searched for.

Search_pattern: Is a regular expression to be matched.

Replacement_string :This is an optional parameter. The matched patterns will be replaced with the Replacement_string in the source string.If not mentioned, the replacement string will be Null.

Start_position:This is an optional parameter. This determines the position in the source string where the search starts. By default, it is 1, which is the starting position of the source string.

Match_occurrence: This is an optional parameter. This determines the occurrence of the search pattern. By default, it is 1, which is the first appearance of the search pattern in the string.

Match_modifiers: This is an optional parameter. This parameter allows us to modify, the matching behavior of the function. The valid range of options is mentioned in the Pattern Matching Modifiers section explained above.

Example queries

Replace a matching string with nothing

SELECT REGEXP_Replace('This is a oracle 11g and 12c substring expression', 'oracle|11g|12c')
 FROM dual;

Output
This is a and substring expression

Replace a SUB string “oracle” with “PGSQL”.

SELECT REGEXP_Replace('This is a oracle 11g and 12c substring expression', 'oracle','PGSQL')
FROM dual;

Output
This is a PGSQL 11g and 12c substring expression

Find groups of word characters ending with “is” and replace with the string “was”.

SELECT REGEXP_Replace('This is a oracle 11g and 12c substring expression', '\w*is','was')
FROM dual;

Output
was is a oracle 11g and 12c substring expression

Remove the white spaces between the strings

SELECT REGEXP_Replace('This is a oracle 11g and 12c substring expression', '[^a-z_A-Z0-9]')
FROM dual;

Output
Thisisaoracle11gand12csubstringexpression

Security Measure-replace the middle numbers of a string with *

SELECT
  regexp_replace( '4024007187788590',
  '(^[[:digit:]]{3})(.*)([[:digit:]]{4}$)', '\1**********\3' )
  credit_card
FROM
  dual;

Output

402**********8590

Remove the spaces that appears more than once in the give input string.

SELECT
  regexp_replace('
  This   is   a   oracle   11g   and   12c   substring   expression  containing    morethan   2   space   between   words '
  , '( ){2,}', ' ' ) regexp_replace
FROM
  dual;

Output

This is a oracle 11g and 12c substring expression containing morethan 2 space between words

Format the give phone number with the pattern +(xx) xxx-xxxx-xxx

SELECT
 
  REGEXP_REPLACE( '+918008344103'
,  '([[:digit:]]{2})([[:digit:]]{3})([[:digit:]]{4})([[:digit:]]{3})',  '(\1) \2-\3-\4' ) phone_number
FROM
  dual

Output

+(91) 800-8344-103

Remove all the alphabets

SELECT REGEXP_Replace('This is a oracle 11g and 12c substring expression', '[[:alpha:]]')
FROM dual;

Output
11 12

Remove all the digits

SELECT REGEXP_Replace('This is a oracle 11g and 12c substring expression', '[[:digit:]]')
FROM dual;

Output

This is a oracle g and c substring expression

Remove special symbols

select regexp_replace('Th∞is St☻ring con♥tains ♫special sy▀mbols','[^a-zA-Z ]') regexp_replace from dual;

Output
This String contains special symbols

Rearrange the sub-strings

select regexp_replace('viswanath annangi','(.*) (.*)','\2 \1') from dual;

Output
annangi viswanath

Add extra space between the characters

select regexp_replace('viswanath annangi','(.)','\1 ') regexp_replace from dual;

Output
v i s w a n a t h a n n a n g i