I have a string 'hello big world,' and I want to remove 'big world' so that the end result string would be just 'hello,'. I didn't know that It would be that hard. The closest I got was:
declare
l_string varchar2(40);
begin
l_string := 'hello big world,';
dbms_output.put_line(l_string);
l_string := regexp_replace(l_string, 'hello (.*),$', '\1');
dbms_output.put_line(l_string); -- it returns 'big world' and that's the part I want to remove
end;
/
It returns big world because that's what your code says. The last parameter in the regexp_replace function is the replacement string. If you want to remove big world then search for it and use an empty string as your replacement, i.e.
regexp_replace(l_string, 'big world', '')
You could use this:
l_string := regexp_replace(l_string, 'hello [^,]*,', 'hello,');
Your code has 2 problems:
First in pattern, you use $, means that oracle only search for the match right before end of string, so for string hello big world, hello big earth, hello big sun oracle will never match the part hello big world, or hello big earth,
Second is that in replace_string you use backreference \1 while you mark reference in the part you want to remove - (.*) in stead of the part you want to keep - hello. If you want to use backreference then it should be regexp_replace(l_string, '(hello) [^,]*,', '\1,');
Refer to docs REGEXP_REPLACE
hello except ,. For following string it will have the same result. regexp_replace('hello big test world,', 'hello [^,]*,', 'hello,')
'hello big world, hello big earth, hello big sun,' ==> 'hello, hello, hello,'