CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
Rajiv A
Rajiv A

Reputation: 943

Replacement portion of regexp_replace oracle

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

Upvotes: 1

Views: 16260

Answers (1)

collapsar
collapsar

Reputation: 17258

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.

Upvotes: 4

Related Questions