Oracle 12.1 example for regular expression replace (regexp_replace) of single quote

Goal: example for regular expression replace.

In real world we wouldnt like the search expression or pattern associated with the text output we need. So let us move on to replace those patterns such that we get what we need.

Candidate Text:

SQL> select ('Hi this is ''ramu'' and here is the ''rose''') string_tosearch from dual;

STRING_TOSEARCH
----------------------------------------
Hi this is 'ramu' and here is the 'rose'

We in the previous blog [http://oracledbaplanner.blogspot.com/2018/09/examples-of-regexpsubstr-usage-in.html] noticed we can use the below sql to filter out the first occurence of a substring covered by single quote.

SQL> select regexp_substr('Hi this is ''ramu'' and here is the ''rose''','''([[:alnum:]]+'')') srch_result from dual;

SRCH_R
------
'ramu'

We have the search pattern which is single quote still reported, so now let us see how can we replace this single quotes such that we get only the text we need.

SQL> select regexp_replace(regexp_substr('Hi this is ''ramu'' and here is the ''rose''','''([[:alnum:]]+'')'),'('')','') replaced_text from dual;

REPL
----
ramu

Now from 'ramu' it came down to ramu.

Thanks