CODEWITHSUNDEEP
regexoracle-databaseregexp-replace
Z.Szymon
Z.Szymon

Reputation: 337

Oracle regexp_replace optional group

I have a string 'SPECIFIC WORD BIG WORLD, SPECIFIC WORD BIG SUN'. The output string should look like this: 'SPECIFIC WORD, SPECIFIC WORD'. In other words, I want to remove everything after 'SPECIFIC WORD' but I want to preserve commas. What I tried so far:

 declare
 l_string varchar2(10000);
 begin
    l_string := 'SPECIFIC WORD BIG WORLD, 
    SPECIFIC WORD BIG SUN, 
    SPECIFIC WORD BIG EARTH';
    dbms_output.put_line(l_string);
    l_string := regexp_replace(l_string, '(SPECIFIC WORD) (BIG)(.*)(,)?', '\1\4');
    dbms_output.put_line(l_string);
 end;
 /

The problem I have is that at the last match, there will be no comma but when I mark this comma as an optional sequence, I get no commas at all. Is there any way around this? I also tried to fix this playing with: https://regex101.com/ but I could not make optional comma behaving as a separate group.

Upvotes: 1

Views: 252

Answers (1)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58461

From what you've described, following should suffice

l_string := regexp_replace(l_string, ' BIG \w+', '');

This just searches for

  • a space
  • followed by BIG
  • followed by a space
  • followed by a Word character, one to unlimited times

Upvotes: 1

Related Questions