Oracle PL/SQL - Using Regular expressions

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. CHAR and VARCHAR2 Variables Size

Introduction

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:

Demo

SQL>
SQL> declare-- from  w  w  w  . j a  v a 2 s  . com
  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>

Related Topic