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-- from ww w .j  a v  a  2s .  c  om
------
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