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.

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 ISIS 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

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.