REGEX using dynamic sql

Question

I have the below regular expression which works perfectly fine using sql, but the same cannot be included in dynamic sql qyery, have tried escaping all the character but it still throws error.

here is what I have tried:

 reg := 'select regexp_replace(data, ||'\.||(docx|pdf|msg)|| ', ||'.\1, ') from table where id=1'

Can you help to include this within dynamic sql?

   select regexp_replace(data, '\.(docx|pdf|msg) ', '.\1, ') from table where id=1;

Aha! Why aren't you using the Correct Answer from your earlier question? One would assume it should be better than the one I gave you (and which you are using here). — , Mar 22 '21 at 21:20

score 0 · Answer 1 · answered Mar 22 '21 at 19:55

It looks that single quotes bother you.

If so, use the q-quoting mechanism, such as:

SQL> declare
  2    reg varchar2(500);
  3  begin
  4    reg := q'[select regexp_replace(data, ||'\.||(docx|pdf|msg)|| ', ||'.\1, ') from table where id=1]';
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>

However, we use dynamic SQL when there's something dynamic. There's nothing dynamic in your query, so - why do you use it? Are you sure you need it? What is the "real" problem you're trying to solve? Maybe it doesn't require dynamic SQL at all.

REGEX using dynamic sql

1 Answers1