I have a regexp_replace where I want to have the replacement portion to be sub part of the replaced string.. In below example /2 should always give the first part i.e MON For example
SELECT REGEXP_REPLACE('Test MON 234','^(.*? )(MON|FRI|SAT|SUN).*$', '\1\2') FROM dual;
==> Test MON
SELECT REGEXP_REPLACE('QA FRI 111','^(.*? )(MON|FRI|SAT|SUN).*$', '\1\2') FROM dual;
should give ==> QA MON
the trivial answer:
SELECT REGEXP_REPLACE('Test MON 234','^(.*? )(MON|FRI|SAT|SUN).*$', '\1MON') FROM dual;
SELECT REGEXP_REPLACE('QA FRI 111','^(.*? )(MON|FRI|SAT|SUN).*$', '\1MON') FROM dual;
the less trivial answer: extract the frozen part of your replacement string from the regexp.
SELECT REGEXP_REPLACE('^(.*? )(MON|FRI|SAT|SUN).*$', '^[^(]*\([^(]*\(([^)|]+).*$', '\1') FROM dual;
note the assumptions of this solution: - you want precisely the first alternative of the second capture group. - no nested capture groups - no escaped capture group delimiters
for anything more complicated (in fact, for this use case, too) you might wish to consider obtaining the frozen replacement from whatever source determines that and not by extracting it from the regex pattern. otherwise there will be a code maintenance nightmare ahead for somebody.
SELECT REGEXP_REPLACE('QA FRI 111','^(.*? )(MON|FRI|SAT|SUN).*$', '\1 MON') FROM dual;?