How REGEXP_LIKE Condition works in Oracle database

REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE. This condition evaluates strings using characters as defined by the input character set.

SQL> select * from scott.emp;

     EMPNO FIRST_NAME           LAST_NAME                SALARY       COMM HIREDATE

---------- -------------------- -------------------- ---------- ---------- ---------

       101 Scott                Tiger                      2000         10 30-SEP-22

       102 John                 Smith                      2000         10 30-SEP-22

       103 Ram                  Hari                       2000         10 30-SEP-22

       104 Rakesh               Sharma                     4000         10 30-SEP-22

Lets say if we want find all employees whose first_name starts with R. Here is how you will do it :

SQL>  select * from scott.emp where regexp_like(FIRST_NAME,'^R');

     EMPNO FIRST_NAME           LAST_NAME                SALARY       COMM HIREDATE

---------- -------------------- -------------------- ---------- ---------- ---------

       103 Ram                  Hari                       2000         10 30-SEP-22

       104 Rakesh               Sharma                     4000         10 30-SEP-22

Lets say if we want find all employees whose first_name starts with R but 'a' in the middle and m at the end, note here the use of paranthesis and $ sign for middle and last alphabet. Here is how you will do it :

SQL>  select * from scott.emp where regexp_like(FIRST_NAME,'^R(a)m$');

     EMPNO FIRST_NAME           LAST_NAME                SALARY       COMM HIREDATE

---------- -------------------- -------------------- ---------- ---------- ---------

       103 Ram                  Hari                       2000         10 30-SEP-22

Lets say if we want find all employees whose first_name end with 'h' , note the use of $ symbol  Here is how you will do it :

SQL> select * from scott.emp where regexp_like(FIRST_NAME,'h$');

     EMPNO FIRST_NAME           LAST_NAME                SALARY       COMM HIREDATE

---------- -------------------- -------------------- ---------- ---------- ---------

       104 Rakesh               Sharma                     4000         10 30-SEP-22

Lets say if we want find all employees whose first_name starts with 'Ra' but it can be Ram or Rakesh so in the middle with paranthesis and pipe we use (m|k) and note it can be anything in the end so here we did not give any last alphabet condition so both Ram and Rakesh are printed. Here is how you will do it :

SQL> select * from scott.emp where regexp_like(FIRST_NAME,'^Ra(m|k)');

     EMPNO FIRST_NAME           LAST_NAME                SALARY       COMM HIREDATE

---------- -------------------- -------------------- ---------- ---------- ---------

       103 Ram                  Hari                       2000         10 30-SEP-22

       104 Rakesh               Sharma                     4000         10 30-SEP-22