Oracle REGEXP_REPLACE pattern

Question

I have a string like "ABCDE ABCDE_ORACLE ABCDE. ORACLE_ABCDE_AAAA" I would like the following result using REGEXP_REPLACE replacing "ABCDE" with "ZZZZZ":

"ZZZZZ ABCDE_ORACLE ZZZZZ. ORACLE_ABCDE_AAAA"

What pattern should I use? Thanks

Answer 1

Tip - Always show what you tried, and give test data set up in a way that makes it easy for people to copy/paste so it's easier to help.

That out of the way, make sure you can describe what you want the regex to do in plain language first. "Replace capital string ABCDE with capital string ZZZZZ where it occurs at the start or end of a sentence". That is what is seen from the test data but is that correct? Is it really "Replace capital string ABCDE with capital ZZZZZ where it occurs anywhere in a string but is it's own word". Is that more accurate? I assume you don't want to replace where it's part of another word like ABCDEFG. Once you fully define the rules, then work on your regex.

This approach uses a WITH statement to create a Common Table Expression (CTE) which in this case can be thought of like an in-line temp table. A great way to set up test data. All you have to do is add more SELECTs with UNION ALLs to create more test strings. It captures each element of the pattern you are looking for in groups numbered sequentially from left to right. Then, replace with the patterns listed by their order but replacing group #2 with the replacement string. So, it replaces string "ABCDE" with "ZZZZZ" on all occurrences on a line where preceded by the start of the line or a space, and followed by a space, period or end of the line.

WITH tbl(str) AS (
  SELECT 'ABCDE ABCDE_ORACLE ABCDE. ORACLE_ABCDE_AAAA' FROM dual
)
SELECT REGEXP_REPLACE(str, '(^| )(ABCDE)( |\.|$)', '\1ZZZZZ\3') AFTER_REPLACE
FROM tbl;