Oracle 19c Regex - Repeating alphabets

Question

I'm working in Oracle 19c. I've a requirement to replace a word with a space if the word contains only the repeating alphabets.

Input - 'AAAA TEST CDDDD TEST'  Output - TEST CDDDD TEST
Input - 'BAD GHHHH TEST XXXX'   Output - BAD GHHHH TEST 

These are some of the sample inputs and expected outputs. So far I've tried these options.

SELECT REGEXP_REPLACE('BAD AAAAA FOR ABBBB', '(\W|^)([A-Z]{3,50})(\W|$)', '') AS result
FROM dual;

SELECT REGEXP_REPLACE('BAD AAAAA FOR ABBBB', '(\b)([A-Z]{3,50})(\b)', '') AS result
FROM dual;

SELECT REGEXP_REPLACE('BAD AAAAA FOR ABBBB', '(\W|^)([A-Z]{3,50})(\W|$)','\1\3')
FROM dual

But none of these helped and (\b) is not supported in Oracle. Is there any solution?

Answer 1

Another simple method is to divide and replace as follows:

WITH INPUT_STRING AS (
    SELECT 'BAD AAAAA FOR ABBBB' STR FROM DUAL
)
SELECT LISTAGG(S_STR, ' ') WITHIN GROUP (ORDER BY LVL) FROM
(SELECT
    STR, REGEXP_SUBSTR(STR, '[^ ]+', 1, LEVEL) S_STR, LEVEL LVL
  FROM
    INPUT_STRING
CONNECT BY
    LEVEL <= REGEXP_COUNT(STR, ' ') + 1) TT
    WHERE REPLACE(S_STR,SUBSTR(S_STR,1,1), NULL) IS NOT NULL;

DB<>Fiddle

If you have multiple string as mentioned by @Barbaros Özhan then you can use the following query:

WITH INPUT_STRING AS (
    SELECT 'BAD AAAAA FOR ABBBB' STR FROM DUAL
    UNION ALL
    SELECT 'BAD GHHHH TEST XXXX' STR FROM DUAL
)
SELECT LISTAGG(S_STR, ' ') WITHIN GROUP (ORDER BY LVL) FROM
    (SELECT
        STR, REGEXP_SUBSTR(STR, '[^ ]+', 1, LVL) S_STR, LVL
      FROM
        INPUT_STRING 
        CROSS JOIN (SELECT LEVEL AS LVL FROM DUAL CONNECT BY LEVEL <= 50)
        WHERE REGEXP_SUBSTR(STR, '[^ ]+', 1, LVL) IS NOT NULL
    ) TT
    WHERE REPLACE(S_STR,SUBSTR(S_STR,1,1), NULL) IS NOT NULL
GROUP BY STR;

DB<>Fiddle