Now I guess I must reply. :)
login as sys and run
$ORACLE_HOME/rdbms/admin/pubpat.sql
$ORACLE_HOME/rdbms/admin/privpat.sql
... 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" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
12/18/2002 11:44 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
*********************************************************************This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank
you.*********************************************************************1
