Oracle Regexp_Replace
Regular expressions provide an efficient way to search for a pattern in a block of text. The Regexp_Replace can be used to search and replace text in required format, for loading data to specific Oracle database columns, format data for display purposes and for data valudation/analysis purposes, which otherwise require several SQL functions and complex code to perform similar tasks. Shown below are some examples to format data using Regexp_Replace function.

Example 1 
SELECT  t_str ||' -> '||
 REGEXP_REPLACE(t_str,
 '([[:lower:]])([[:upper:]])', '\1 \2') 
   regexp_replace
FROM (SELECT 'HelloWorld' t_str FROM DUAL);
Regexp_Replace Spliting String
 
Example 2 
SELECT  t_str ||' -> '||
 REGEXP_REPLACE(t_str,
 '([[:lower:]])([[:upper:]])', '\1_\2')
   regexp_replace
FROM (SELECT 'RegexpReplace()' t_str FROM DUAL);
Regexp_Replace Spliting String
 
Example 3 
SELECT t_str ||' -> '||  REGEXP_REPLACE(t_str,
  '([[:alpha:]]{1,})','+ ') replace_to_plus
FROM (SELECT '1138t3801db1872ddi' t_str FROM DUAL);
Regexp_Replace Replace with Plus
 
Example 4 
SELECT ph_no ||' -> '|| REGEXP_REPLACE(ph_no,
'([[:digit:]]{3})([[:digit:]]{3})([[:digit:]]{4})',
'(\1) \2-\3 ') "REGEXP_REPLACE -> USA Format"
FROM (SELECT 'Phone: 1112223333x1234' ph_no FROM DUAL);
Regexp_Replace To format Phone Number


Oracle registered trademark of Oracle Corporation.
Last Revised on: March 01, 2012

  74110