Oracle REGEXP_REPLACE function


The REGEXP_REPLACE function is used to return source_char with every occurrence of the regular expression pattern replaced with replace_string. The string returned is in the same character set as source_char. It returns VARCHAR2 if the first argument is not a LOB and returns CLOB if the first argument is a LOB.

Syntax :

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


Name Description
source_char source_char is a character expression that serves as the search value. It is commonly a character column and can be of any of the data types  CHAR,  VARCHAR2,  NCHAR,  NVARCHAR2, CLOB  or NCLOB.
pattern pattern is the regular expression. It is usually a text literal and can be of any of the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can contain up to 512 bytes. If the data type of pattern is different from the data type of source_char, then Oracle Database converts the pattern to the data type of source_char.
replace_string replace_string can be of any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. If replace_string is a CLOB or NCLOB, then Oracle truncates replace_string to 32K. The replace_string can contain up to 500 backreferences to subexpressions in the form \n, where n is a number from 1 to 9. If n is the backslash character inreplace_string, then you must precede it with the escape character (\\).

the position is a positive integer indicating the character of source_char where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character of source_char.

occurrence occurrence is a nonnegative integer indicating the occurrence of the replace operation:
  • If you specify 0, then Oracle replaces all occurrences of the match.
  • If you specify a positive integer n, then Oracle replaces the nth occurrence.

If the occurrence is greater than 1, then the database searches for the second occurrence beginning with the first character following the first occurrence of the pattern, and so forth.

match_param match_parameter is a text literal that lets you change the default matching behavior of the function. This argument affects only the matching process and has no effect on replace_string. You can specify one or more of the following values for match_parameter:

Regular Expression References

Flags Reference:

Flags Description
i ignore case
c case sensitive
n match any character as well as match newline character
m multi-line
x ignore whitespace

Quantifiers/Alternative Classes:

Character Description
. Any character except newline
 Example - Matches any character
* Matches O or more preceding character 
Example. b* - bbbeee
+ Matches 1 or more preceding character 
Example. b+ - bbbeee, beee
? Matches either 0 or 1 preceding character, effectively matches is optional 
Example. Goog?le - Goole , Google
| Represent like a boolean OR for alternative matches 
Example. AB|CD - match ab or cd

Grouping Classes:

Character Description
[ ] Matches any character in the set
Example. [ABC] - matches any of a, b, or c
( ) Capture groups of sequence character together
Example. (name) - matches sequence of group character

Ranging Classes:

Character Description
{a} matches exactly m time
Example. b{1} - match exactly 1 time
{a,} matches exactly m or more time
Example. b{1,} - match exactly 1 or more time
{a, z} matches m to n times
Example. b{3,5} - match between 3 & 5

Escape Character Classes:

Character Description
\ specified the next special character
Example. \\ - Matches a "\" character.
\n Matches n number (digit 1 to 10) LINE FEED character enclosed between ( and ).

Anchors Classes:

Character Description
^ Beginning of the string. If more then one line matches any beginning line.
Example. ^ABC - starting character A then match ABC
$ Ending of the string. If more then one line matches any ending line.
Example. ABC$ - ending character C then match ABC
\A Matches only at the beginning of the string.
Example. h\A - hello Opal! (matches only 'hello')
\Z Matches only at the ending of the string.
Example. o\A - hello Opal! (matches only 'hello')

Character Classes:

Character Description
\d Matches digit character Example. \d - Hello123 (matches only '123')
\D Matches non digit character Example. \d - Hello123 (matches only 'Hello')
\w Matches word character Example. \w - Hello123###/* (matches only 'Hello123')
\W Matches non word character Example. \W - Hello123###/* (matches only '###/*')
\s Matches whitespace 
Example. \s - Hello 123 ### (matches only whitespace)
\S Matches non whitespace 
Example. \S - Hello 123 ### (matches non whitespace 'Hello' and '123' and '###')

Examples: Oracle REGEXP_REPLACE function

The following example examines phone_number, looking for the pattern xxx.xxx.xxxx. Oracle reformats this pattern with (xxx) xxx-xxxx

'(\1) \2-\3') "REGEXP_REPLACE"
FROM employees



(515) 123-4444
(515) 123-4567
(515) 123-4568
(515) 123-4569
(515) 123-5555
(515) 123-7777
(515) 123-8080
(515) 123-8181
(515) 123-8888
(515) 124-4169
(515) 124-4269
(515) 124-4369
(515) 124-4469
(515) 124-4567
(515) 124-4569
(515) 127-4561
(515) 127-4562
(515) 127-4563
(515) 127-4564
. . .

The following example examines country_name and puts a space after each non-null character in the string.

REGEXP_REPLACE(country_name, '(.)', '\1 ') "REGEXP_REPLACE"
FROM countries;

Output :


A r g e n t i n a
A u s t r a l i a
B e l g i u m
B r a z i l
C a n a d a
S w i t z e r l a n d
C h i n a
G e r m a n y
D e n m a r k
E g y p t
F r a n c e
I s r a e l
I n d i a
I t a l y
J a p a n
K u w a i t
M a l a y s i a
M e x i c o
N i g e r i a
N e t h e r l a n d s
S i n g a p o r e
U n i t e d   K i n g d o m
U n i t e d   S t a t e s   o f   A m e r i c a
Z a m b i a
Z i m b a b w e

The following example examines the string, looking for two or more spaces. Each occurrence of two or more spaces will be replaced by a single space.

REGEXP_REPLACE('the   web     development    tutorial  w3resource.com',
'( ){2,}', ' ') "REGEXP_REPLACE"

Sample Output:

the web development tutorial w3resource.com

Previous: NLSSORT

Follow us on Facebook and Twitter for latest update.