Oracle-L: RE: Regular Expressions in SQL

From: <Jared.Still_at_radisys.com>
Date: Wed, 18 Dec 2002 15:39:24 -0800
Message-ID: <F001.0051DB5A.20021218153924@fatcity.com>

Now I guess I must reply. :)

... to create the owa_pattern package.

Here are some examples of its use.

declare

tstr varchar2(100) := 'this contains tabs multiple spaces and single spaces';
begin

   dbms_output.put_line( tstr);
   owa_pattern.change( tstr, '\s', '', 'g');
   dbms_output.put_line( tstr);

end;
/

declare

tstr varchar2(100) := 'this c34ontains s0239everal 2340 numeric 882 dig2its';
begin

dbms_output.put_line( tstr);

remove the digits owa_pattern.change( tstr, '\d', '', 'g'); dbms_output.put_line( tstr);
remove the extra spaces owa_pattern.change( tstr, '\s+', ' ', 'g'); dbms_output.put_line( tstr); end;
/

drop table owatest;

create table owatest (

test varchar2(20)
)
/

insert into owatest values('non numeric row'); insert into owatest values('numeric 23423 row');

commit;

select *
from owatest
where owa_pattern.amatch(test,1,'^.*\d') > 0
/

drop table regex;

create table regex (

test varchar2(20)
);

create or replace function strip_str (

data_in varchar2
--, regex_in varchar2
)
return varchar2
is

test_str varchar2(4000);
begin

test_str := data_in;

   --owa_pattern.change(test_str, regex_in, '', 'g');
   owa_pattern.change(test_str, '\x0a', '', 'g');
   owa_pattern.change(test_str, '\x0c', '', 'g');
   owa_pattern.change(test_str, '\x0d', '', 'g');

return test_str;

end;
/

show error function strip_str

insert into regex values( 'carriage' || chr(13) || 'return');
insert into regex values( 'line' || chr(10) || 'feeds' || chr(10));
insert into regex values( 'form feed' || chr(12));

commit;

select test
from regex;

select strip_str(test) test
from regex
/

For more information on regex:

http://www.cc.gatech.edu/classes/RWL/Projects/citation/Docs/Design/regex.intro.1.doc.html http://www.cc.gatech.edu/classes/RWL/Projects/citation/Docs/Design/regex.intro.1.doc.html

They are not strictly a Perl thing. If you've ever used egrep, you've used regular expressions.

Jared

"Jamadagni, Rajendra" <Rajendra.Jamadagni_at_espn.com> Sent by: root_at_fatcity.com
12/18/2002 11:44 AM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE: Regular Expressions in SQL

I think some OWA packages would let you do basic regex ... but for an complete answer wait for Jared's answer. This is his favorite topic 8:). Really.
Raj

Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!

-----Original Message-----
Sent: Wednesday, December 18, 2002 2:04 PM To: Multiple recipients of list ORACLE-L

What's the easiest way to do regular expressions in an SQL query? If the answer is creating a function, does anyone have one they wouldn't mind
sharing?
Thanks,
Chris
Christopher Beckley
OCPDBA, MCDBA, MCSD, EIEIO
ThirdParadigm LLC
cbeckley_at_thirdparadigm.com

-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: cbeckley_at_thirdparadigm.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Jared.Still_at_radisys.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

text/plain attachment: ESPN_Disclaimer.txt

Received on Wed Dec 18 2002 - 17:39:24 CST