REGEXP REPLACE

REGEXP_REPLACE is an SQL function that can do string replacements based on a regular expression.

Examples

Replace all letter O's with the number 0:

SELECT REGEXP_REPLACE(col1, 'O', '0') FROM tab1;

Remove all special (unprintable) characters from a string:

SELECT REGEXP_REPLACE(col1, '[[:cntrl:]]', ' ') FROM tab1;

Parse a string into components:

SELECT REGEXP_REPLACE('18.0.1', '(\d+)\.(\d+)\.(\d+)', '\1') FROM dual;
SELECT REGEXP_REPLACE('18.0.1', '(\d+)\.(\d+)\.(\d+)', '\2') FROM dual;
SELECT REGEXP_REPLACE('18.0.1', '(\d+)\.(\d+)\.(\d+)', '\3') FROM dual;

Also see

• REPLACE, the normal string replace function.
• TRANSLATE, replace characters in a string, one char at a time.