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.

Examples[edit]

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

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