Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: regular expressions
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
SQL> SQL> SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Something" AS2 package uk.tessella;
4 import java.util.regex.*; 5 import java.math.BigDecimal; 6 import java.sql.SQLException;
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;
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
A
SQL> SQL> --Find all words that contain a vowel SQL> --------------------------------------------------- SQL> SELECT a
A
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
![]() |
![]() |