Bullying colleagues…. with regular expressions

on
declare

  l_where varchar2(4000)
         := 'AND aaa                 =  zzz    '
          ||'     AND bbb                 =  xxx     '
          ||' AND ccc   BETWEEN   ddd AND eee'
          ||'     AND SUBSTR( fff,  1,  jjj  )  = kkk  ';

begin

  l_where := regexp_replace(l_where,'([ ])[ ]*','\1');                        --*1
  l_where := regexp_replace(l_where,'[ ]*([=,<>()])[ ]*','\1');               --*2
  l_where := regexp_replace(l_where,'[ ](BETWEEN|LIKE|IN|IS|NOT)[ ]','#\1#'); --*3
  l_where := regexp_replace(l_where,'(AND|OR)[ ]','\1#');                     --*4
  l_where := regexp_replace(l_where,'(BETWEEN#.*)[ ]([^#].*AND)','\1#\2');    --*5

  dbms_output.put_line(l_where);

end;

*1 remove double spaces
*2 remove spaces around characters  = ,  < >  (  )
*3 replace spaces around words  BETWEEN LIKE IN IS and NOT with an #
*4 replace space behind AND or OR with an #
*5 replace space before AND followed on BETWEEN with an #

These 5 lines of regular expression can replace 95 lines of nested if then elses and some string readings (don’t know what is more horrible to read, my regexp or those lines).

It could have been even more compact (merge rule 1 and 2). But hey, I want to undestand it myself as well.