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> 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.


Related Topic