Regular expression to extract UPPERCASE words between given start and end strings

Question

I am trying to replace "MR JOE JOE" to (NAME) in the given sentence starting with "Transfer from" and ending with "CREDIT TO ACCOUNT" using the following code in Snowflake:

select regexp_replace('Transfer From MR JOE JOE to PayID Phone CREDIT TO ACCOUNT', '(.*Transfer From)|(CREDIT TO ACCOUNT)|\\b([A-Z]{1,})\\b|(\\b(to \\w+\\w+)\\b)',
'\\1 (NAME) \\5 \\2');

The output I get is as follows:

Transfer From (NAME) (NAME) (NAME) (NAME) (NAME) to PayID Phone (NAME) CREDIT TO ACCOUNT

Desired Output:

Transfer From (NAME) to PayID Phone CREDIT TO ACCOUNT

I couldn't make the right pattern for matching groups and replacements. How do I do it? Any suggestions please?

Answer 1

Here's how I'd do it in Oracle given the information you posted. Perhaps this would apply to your RDBMS?

select 
  regexp_replace('Transfer From MR JOE JOE to PayID Phone CREDIT TO ACCOUNT', 
  '(.*Transfer From )(.*)( to .* CREDIT TO ACCOUNT)',
  '\1(NAME)\3') replace
from dual;


REPLACE                                              
-----------------------------------------------------
Transfer From (NAME) to PayID Phone CREDIT TO ACCOUNT