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.