Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Eddie.

Asked: March 06, 2018 - 8:27 pm UTC

Last updated: March 08, 2018 - 2:43 am UTC

Version: 12.1.0.1.0

Viewed 1000+ times

You Asked

I'm trying to eliminate some parts of a string with regexp_replace and try a lot of regular expressions but I can't have the desire result. I'm using a code similar to the following:

Declare
w_times number;
w_source varchar2(2000);
w_target varchar2(2000);
w_regexp varchar2(100);
Begin
w_regexp := '(<script>.+</script>)?';
w_source := '<script>script text</script> Some normal text. <script>script text</script> More normal text. <script>script text</script> ';
w_times := regexp_count(w_source,w_regexp);
dbms_output.put_line('Found: ' || w_times || ' times pattern '|| w_regexp);
w_target := w_source;
for i in 1 .. w_times loop
w_target := regexp_replace(w_target,w_regexp,'',1,1,'n');
dbms_output.put_line('Target: ' || w_target);
dbms_output.put_line('Source:' || w_source);
end loop;
End;

When I run it (I prove it on sqlLive too) with the regexp_count I have an answer of 3 counts of the pattern, but the regexp_replace return me a blank line even in the first run of the for sentence, so the regexp_replace is getting all between the first <script> tag and the last </script> tag.

How can I do to eliminate only <script>script text</script> each time and keep all other text?

Same occurs if I use: "w_target := regexp_replace(w_target,w_regexp,'',1,0,'n');" without a for sentence.

and Connor said...

<script>.+</script>

effectively says:

"Find <script> ... then search as far as possible looking for </script>"

The key thing there is "as far as possible", which ultimately ends up at the very last part of the source text

SQL> with src as
  2   ( select '<script>script text</script> Some normal text. <script>script text</script> More normal text. <script>script text</script>@' s from dual )
  3  select  regexp_replace(s,'(<script>.+</script>)?')
  4  from src;

R
-
@


You want to move your greedy indicator to where the "as far possible" scanning is occurring, ie, the "+". Hence

SQL> with src as
  2   ( select '<script>script text</script> Some normal text. <script>script text</script> More normal text. <script>script text</script>@' s from dual )
  3  select  regexp_replace(s,'(<script>.+?</script>)')
  4  from src;

REGEXP_REPLACE(S,'(<SCRIPT>.+?</SCRIPT>
---------------------------------------
 Some normal text.  More normal text. @


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Eddie Molina, March 07, 2018 - 3:38 am UTC

Thanks a lot Connor. As always, all you're the best!
Connor McDonald
March 08, 2018 - 2:43 am UTC

glad we could help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library