Gotcha of regular expression

 Lately, I issued a query, of that I was sure, it would return records. But it did not, obviously. The query used a regexp_like in the where clause. It turns out that the filter column values sometimes contained linebreaks (which were not found) and sometimes not (which were found). Now, thinking about it, it is obvious but initially, I was rather confused. The following is a query example for the not-returning of records that surprised me.

select *
  from dual
 where regexp_like(q'BEGIN insert into own_datenstatus.uc4trigger (jobplan, created) values ('DATASTORE.JP.BEBU_MART_KPRG_MONTHLY',sysdate); commit;
    NULL;
EXCEPTION
    WHEN OTHERS THEN
        NULL;
END;'
                  ,'^.*kprg.*$'
                  ,'i');

My oversight was that I used ^ and $, marking the beginning and ending of the string to examine. By default, the dot does not match with linebreaks, thus the regular expression would not match. To fix this, you have thee choices.

• Remove the ^ and $

  select *
    from dual
   where regexp_like(q'BEGIN insert into own_datenstatus.uc4trigger (jobplan, created) values ('DATASTORE.JP.BEBU_MART_KPRG_MONTHLY',sysdate); commit;
      NULL;
  EXCEPTION
      WHEN OTHERS THEN
          NULL;
  END;'
                    ,'.*kprg.*'
                    ,'i');
  

• Add the n to the match parameters. It makes the dot match linebreaks too.

  select *
    from dual
   where regexp_like(q'BEGIN insert into own_datenstatus.uc4trigger (jobplan, created) values ('DATASTORE.JP.BEBU_MART_KPRG_MONTHLY',sysdate); commit;
      NULL;
  EXCEPTION
      WHEN OTHERS THEN
          NULL;
  END;'
                    ,'^.*kprg.*$'
                    ,'in');

• Add the m to the match parameters. It makes the ^ and $ to match start and end of a line.

  select *
    from dual
   where regexp_like(q'BEGIN insert into own_datenstatus.uc4trigger (jobplan, created) values ('DATASTORE.JP.BEBU_MART_KPRG_MONTHLY',sysdate); commit;
      NULL;
  EXCEPTION
      WHEN OTHERS THEN
          NULL;
  END;'
                    ,'^.*kprg.*$'
                    ,'im');

All should find any row where the values contains a line with kprg. I am not aware of any differences with respect to the result. However, I suppose that the last is more performant than the others, because the searched string can be split into lines for the search.