This is an
excerpt from the book
Advanced PL/SQL: The Definitive Reference by Boobal Ganesan.
Syntax:
regexp_replace( source, pattern, replace string, position,
occurrence, options)
The source can
be a string literal, variable, or column. The pattern
is the expression to be replaced. The replace string
is the text that will replace the matching patterns.
The optional position defines the location to begin
searching the source string. This defaults to 1.
The optional occurrence defines the occurrence of the
pattern that you want replaced. This defaults to 0
(all occurrences). Setting this to a positive number
will result in only that occurrence being replaced.
The matching options are the same.
select
regexp_replace('We are driving south by south east',
'south', 'north')
from dual;
Oracle 10g introduced regular expression
functions in SQL with the functions REGEXP_SUBSTR,
REGEXP_REPLACE, REGEXP_INSTR and REGEXP_LIKE.
SELECT
ENAME,
REGEXP_SUBSTR(ENAME,'DAM') SUBSTR,
REGEXP_INSTR(ENAME, 'T') INSTR,
REGEXP_REPLACE(ENAME,'AM','@') REPLACE,
REGEXP_COUNT(ENAME, 'A') COUNT
FROM
EMP
WHERE
REGEXP_LIKE(ENAME,'S');
ENAME
SUBSTR
INSTR REPLACE
COUNT
---------- ---------- ---------- ----------
----------
SMITH
4 SMITH
0
JONES
0 JONES
0
SCOTT
4 SCOTT
0
ADAMS
DAM
0 AD@S 2
JAMES
0 J@ES
1
REPLACE replaces all occurrence of a
string. REGEXP_REPLACE has the same behavior by default, but
when the fifth parameter, OCCURRENCE, is set to a value
greater than zero, the substitution is not global.
SELECT
REGEXP_REPLACE
(
'Programming',
'([[:alpha:]]+)[[:space:]]([[:alpha:]]+)',
'\2: \1',
1,
1
)
FROM
DUAL;
REGEXP_REPLACE('ADVANCEDORACLESQ
--------------------------------
Oracle: Advanced SQL
Programming
This function is a successful
extension of both the REPLACE and the TRANSLATE function.
This function was introduced in the Oracle version 10g,
which replaces a
specific portion of the source string using a user
customized regular expression based search
pattern.
The prototype for the REGEXP_REPLACE
function is shown below,
REGEXP_REPLACE(<Source_string>, <Search_pattern>[,
<Replacement_string>[, <Start_position>[, <Match_occurrence>[,
<Match_modifiers>]]]])
·
Source_string:
The string to be searched for.
·
Search_pattern:
The regular expression pattern that is to be searched for in
the source string. This can be a combination of the POSIX
and the Perl-influenced metacharacters mentioned in the
above section.
·
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.
String Removal
The below statement has a mix of
numbers, alphabets and punctuations in its source string.
The first match pattern selects all the alphabets, the
second match pattern selects all the digits and the third
match pattern selects all the punctuations, removes them
from the source string respectively, and prints the result.
WITH
t AS
( SELECT'a0b1c2d3e4f5g6h7i8j9k!l@m#n$o%p^q&r*s(t)u_v+w-x=y[z]'
col FROM dual)
SELECT
regexp_replace(col,'[[:alpha:]]')
without_alphabets,
regexp_replace(col,'[[:digit:]]') without_digits,
regexp_replace(col,'[[:punct:]]')
without_punctuations
FROM
t;
Result:
WITHOUT_ALPHABETS
0123456789!@#$%^&*()_+-=[]
WITHOUT_DIGITS
abcdefghijk!l@m#n$o%p^q&r*s(t)u_v+w-x=y[z]
WITHOUT_PUNCTUATIONS
a0b1c2d3e4f5g6h7i8j9klmnopqrstuvwxyz
Symbol Removal
The below snippet removes all the
special symbols from the source string and prints only the
readable characters. This query is of much help while saving
the emails and
documents into the database by removing the unreadable
special symbols as a data cleaning process.
select regexp_replace('Th∞is
St☻ring con♥tains ♫special sy▀mbols','[^a-zA-Z ]')
regexp_replace from dual;
Result:
This String contains special symbols
Space Removal
The below statement removes the
spaces which are present more than once in the source
string. This script plays a vital role in removing the
unwanted spaces from a document or an email before storing
them permanently in the database.
select regexp_replace('This
string
contains
more
than
one
spacing
between
the
words','( ){2,}',' ') regexp_replace from dual;
Result:
This string contains more than one
spacing between the words
Name Rearrange
The below listing re-arranges the
last name and the first name from the source string in a
user required format
for reporting purposes.
select regexp_replace('Randy
Orton','(.*) (.*)','\2, \1') regexp_replace from dual;
Result:
Orton, Randy
Space Addition
The below SQL adds an extra space in
between each character from the source string as shown
below,
select regexp_replace('abcdefghijklmnopqrstuvwxyz','(.)','\1
') regexp_replace from dual;
Result:
a b
c d e f g h i j k l m
n o p q r s t u v w x y z
Security Measure
The below statement hides the middle
portion of a number as a security measure. This process is
done by the banking industry during the
account-related
communication by hiding the middle portion of the credit
card, account number, phone number and by the email servers
for OTP generation for user confirmation and security check.
select
regexp_replace('91105434563452345623',
'(^[[:digit:]]{4})(.*)([[:digit:]]{4}$)',
'\1**********\3') regexp_replace from dual;
Result:
9110**********5623
 |
Need to learn to program with PL/SQL?
For complete notes on programming in PL/SQL, we
recommend the book
Advanced PL/SQL: The Definitive Reference by Boobal Ganesan.
This is a complete book on PL/SQL with
everything you need to know to write efficient and
complex PL/SQL code. |
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
