REGEXP REPLACE

From Oracle FAQ
Jump to: navigation, search

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

[edit] 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;

[edit] Also see

  • REPLACE, the normal string replace function.