Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: regular expressions

Re: regular expressions

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 05 Jan 2006 09:35:11 -0800
Message-ID: <1136482508.658323@jetspin.drizzle.com>


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

To a limited extent you can find support in the OWA_PATTER built-in package.

So, for example, the following are equivalent:

CREATE OR REPLACE FUNCTION owa_match(phone# VARCHAR2) RETURN BOOLEAN IS BEGIN
   RETURN owa_pattern.MATCH(phone#,'^[0-9]{3}-[0-9]{3}-[0-9]{4}$'); END owa_match;
/

CREATE OR REPLACE FUNCTION regexp (string_in VARCHAR2) RETURN BOOLEAN IS BEGIN
   IF NOT REGEXP_LIKE
(string_in,'^([[:digit:]]{3}-[[:digit:]]{3}-[[:digit:]]{4}|[[:digit:]]{10})$')

   THEN
     RETURN False;
   ELSE
     RETURN True;
   END IF;
END regexp;
/

Of course the REGEXP runs in 140 seconds on my test data whereas the OWA package takes 6921 but what's a little time when you are still in 9i? ;-)

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Thu Jan 05 2006 - 11:35:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US