Re: regular expressions

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 5 Jan 2006 07:08:51 -0800
Message-ID: <1136473731.191025.175030@z14g2000cwz.googlegroups.com>

T wrote:
> Is any way to use regular expressions on 9.X Oracle databases, and how?
> Or it is 10g feature???

Hi there,

New to 10g are the REGEXP_LIKE operator and functions that include REGEXP_INSTR and REGEXP_SUBSTR. These are not available in 9i and AFAIK you will have to roll your own. I have coded a very simplistic example below using a little Java to get you on the right track. Expand/enhance as appropriate. It has been developed on 10g but should function without event on 9i (I do not have a 9i instance at hand).

Kind regards
Mike

---

Michael D O'Shea
http://www.strychnine.co.uk

SQL>
SQL>
SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Something" AS

  2 package uk.tessella;
  3

  4   import java.util.regex.*;
  5   import java.math.BigDecimal;
  6   import java.sql.SQLException;

  7
  8 public class TestJRegEx
  9 {
 10 public static BigDecimal regexFind(String re,String toFind) throws SQLException
 11 {

 12      Matcher m;
 13      BigDecimal found;
 14      try
 15      {
 16       Pattern p=Pattern.compile(re);
 17       m=p.matcher(toFind);
 18       found=m.find()?new BigDecimal(1):new BigDecimal(0);
 19      }
 20      catch (Exception e)
 21      {
 22       throw new SQLException("something ..."+e.toString());
 23      }
 24      return found;

 25 }
 26 }
 27 /

Java created.

SQL>
SQL> CREATE OR REPLACE PACKAGE pkgyourRegexFunctions AS   2 FUNCTION regexPresent(regEx IN VARCHAR2,toMatch IN VARCHAR2) RETURN NUMBER
  3 IS LANGUAGE JAVA NAME
'uk.tessella.TestJRegEx.regexFind(java.lang.String,java.lang.String) returns java.math.BigDecimal';
  4 END pkgyourRegexFunctions;
  5 /

Package created.

SQL>
SQL> CREATE TABLE tblTest(a VARCHAR2(100));

Table created.

SQL> INSERT INTO tblTest
  2 VALUES ('Tessella');

1 row created.

SQL> INSERT INTO tblTest
  2 VALUES ('Support');

1 row created.

SQL> INSERT INTO tblTest
  2 VALUES ('Services');

1 row created.

SQL> INSERT INTO tblTest
  2 VALUES ('plc');

1 row created.

SQL>
SQL>
SQL>
SQL>
SQL> --Find all words that start with "T" and end in "a"
SQL> ---------------------------------------------------
SQL> SELECT a

  2 FROM tblTest
  3 WHERE pkgyourRegexFunctions.regexPresent('^T.*a',a)=1;

A

---

Tessella

SQL>
SQL> --Find all words that contain a vowel
SQL> ---------------------------------------------------
SQL> SELECT a

  2 FROM tblTest
  3 WHERE pkgyourRegexFunctions.regexPresent('[aeiou]',a)=1;

A

---

Tessella
Support
Services

SQL> SELECT *
  2 FROM V$VERSION; BANNER

---

Personal Oracle Database 10g Release 10.1.0.2.0 - Production

PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production

NLSRTL Version 10.1.0.2.0 - Production

SQL>
SQL> SPOOL OFF Received on Thu Jan 05 2006 - 09:08:51 CST