Search code examples
regexoracle-databaseregexp-replace

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.

Solution

  • 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