Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: Regular Expressions - REGEXP_REPLACE Jump to:  
Category: >> Oracle PL/SQL >> Regular Expressions - REGEXP_REPLACE Bookmark and Share

<< lastnext >>

Snippet Name: Regular Expressions - REGEXP_REPLACE

Description: The REGEXP_REPLACE function works by looking for
an occurrence of a regular expression and replacing it with the contents of a
supplied text literal. The replacement text literal can also contain backreferences to
subexpressions included in the match giving extremely granular control over your
search and replace operations.

Also see:
» Regular Expressions - REGEXP_SUBSTR
» Regular Expressions - REGEXP_LIKE
» Regular Expressions - RegExp examples ...
» Regular Expressions - RegExp examples ...
» Regular Expressions - RegExp examples...
» Regular Expressions - Regexp Cheat She...
» RegExp - Append first name first lette...

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 14th, 2009

REGEXP_REPLACE(source_string, pattern
[, replace_string [, position
[,occurrence, [match_parameter]]]])
 
 
-- collapse multiple spaces to a single space:
SELECT 
REGEXP_REPLACE('some    text    with lots     of    extra spaces', 
'( ){2,}', ' ') 
RESULT FROM dual;
 
 
 
-- The following query replaces any two or more spaces 
-- with a single space. The ( ) subexpression contains a single 
-- space, which can be repeated two or more times, as indicated 
-- by {2,}.
 
SELECT REGEXP_REPLACE('Joe   Smith',
       '( ){2,}', ' ')
       AS RX_REPLACE
  FROM dual
RX_REPLACE
----------
Joe Smith 
 
 
-- Replace a period with a string to extend the sentence: 
SELECT REGEXP_REPLACE('I cannot see you.','\.',' in the dark.') 
REGEXT_SAMPLE
FROM dual;
 
 
-- The next example shows the name 'Mike Steven Rogers' transformed 
-- to Rogers, Mike Steven, by referring to the individual 
-- subexpressions by number:
 
SELECT REGEXP_REPLACE(
       'Mike Steven Rogers',
       '(.*) (.*) (.*)', '\3, \1 \2')
  FROM dual
REGEXP_REPLACE('EL
------------------
Rogers, Mike Steven
 
 
-- The following example shows use of the REGEP_SUBSTR function 
-- to find any duplicate occurrences of alphanumeric values 
-- separated by a space. The displayed result shows the 
-- substring that identifies the duplicated words 'IS'.
 
SELECT REGEXP_SUBSTR(
       'The final test IS IS the implementation',
       '([[:alnum:]]+)([[:SPACE:]]+)\1') AS substr
  FROM dual
SUBSTR
------
is is 
 
 
-- With REGEXP_REPLACE it is simple to filter out certain 
-- parts of data. This example shows how a very simple HTML 
-- filter could be written.
SELECT REGEXP_REPLACE (c1, ‘<[^>]+>’)
FROM t1
 
 
-- insert a space between each character. With REGEXP_REPLACE 
and backreferences, we are able to replace every letter by 
-- itself followed by a space:
 
CREATE VIEW v1 AS
SELECT empno,
REGEXP_REPLACE(email, '(.)', '\1) email
FROM emp;
SELECT email FROM v1 WHERE empno = 7369;
-> j d o e @ s o m e w h e r e . c o m
 
 
 
 
 
 
 
 


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 173 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?