Regular expressions allow you to search for patterns in string data by using a very rich syntax.
Regular expressions cannot be used as parameters in the standard Oracle built-in text search functions: LIKE, SUBSTR, INSTR, and REPLACE.
Instead,regular expressions have their own versions of the same functions: REGEXP_LIKE, REGEXP_SUBSTR, REGEXP_INSTR, and REGEXP_REPLACE.
In regular expressions, the special character | defines an OR condition for the characters surrounding it, as shown here:
SQL> SQL> declare-- w ww .j a va 2s . c om 2 v1_tx VARCHAR2(2000):='*ABC*BBC*'; 3 begin 4 DBMS_OUTPUT.put_line('First hit:'|| 5 REGEXP_INSTR (V1_TX,'A|BBC',1,1)); 6 DBMS_OUTPUT.put_line('Second hit:'|| 7 REGEXP_INSTR (V1_TX,'A|BBC',1,2)); 8 end; 9 / First hit:2 Second hit:6 PL/SQL procedure successfully completed. SQL>