Oracle SQL - REGEXP_LIKE Pattern to match arbitrary characters and ignoring cases

Introduction

The following code shows how to use REGEXP_LIKE Function to specify a pattern that:

  • the first character is arbitrary,
  • followed by at least one and at most two a characters,
  • followed by one or more arbitrary characters,
  • while ignoring the differences between uppercase and lowercase.

REGEXP_LIKE is a Boolean function; its result is TRUE or FALSE.

Demo

SQL>
SQL> select 'found!' as result from dual
  2  where regexp_like('bar', '^.a{1,2}.+$', 'i');

RESULT--   w w  w  .j a v  a  2 s .c  o  m
------
found!

SQL>
SQL> select 'found!' as result from dual
  2  where regexp_like('BAR', '^.a{1,2}.+$', 'i');

RESULT
------
found!

SQL>
SQL> select 'found!' as result from dual
  2  where regexp_like('ba', '^.a{1,2}.+$', 'i');

no rows selected

SQL>

Related Topic