Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / PL SQL Data Types

 

Extending your options with regular expressions

Regular expressions cannot be used as parameters in the standard Oracle built-in text search functions: LIKE, SUBSTR, INSTR, and REPLACE. Regular expressions have their own versions of the same functions: REGEXP_ LIKE, REGEXP_SUBSTR, REGEXP_INSTR, and REGEXP_REPLACE. SQL> declare   2      v1_tx VARCHAR2(2000):='*ABC*BBC*';   3  begin   4      DBMS_OUTPUT.put_line('First hit:'|| REGEXP_INSTR(V1_TX,'A|BBC',1,1));   5      DBMS_OUTPUT.put_line('Second hit:'|| REGEXP_INSTR(V1_TX,'A|BBC',1,2));   6  end;   7  / First hit:2 Second hit:6 PL/SQL procedure successfully completed. SQL>