RE: OWA Pattern Match
- From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
- To: "Gogala, Mladen" <Mladen.Gogala@xxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 17 Sep 2004 10:37:40 -0700
Thanks, but, pray tell, "Does it work?"
Ian=20
-----Original Message-----
From: Gogala, Mladen [mailto:Mladen.Gogala@xxxxxxxx]=20
Sent: Friday, September 17, 2004 7:26 AM
To: MacGregor, Ian A.; oracle-l@xxxxxxxxxxxxx
Subject: RE: OWA Pattern Match
Try REGEXP_LIKE instead of OWA_PATTERN. Jonathan Gennick wrote a little =
booklet which explains how to use regular expressions with Oracle. In =
your case, it's a plug in replacement for OWA_PATTERN.MATCH. It would go =
like
this:
declare
preprintregex VARCHAR2(30) :=3D '^[a-z]+(\-[a-z]+)*\/\d{7,9}$';
i integer :=3D3D 0;
begin
IF REGEXP_LIKE('hep-ex/0408086', preprintregex) THEN
null;
else
i :=3D 1/i;
end if;
end;
/
Here is an excerpt from the manual:
REGEXP_LIKE
REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE =
performs regular expression matching instead of the simple pattern =
matching performed by LIKE. This condition evaluates strings using =
characters as defined by the input character set.
This condition complies with the POSIX regular expression standard and =
the Unicode Regular Expression Guidelines. For more information, please =
refer to Appendix C, " Oracle Regular Expression Support".
regexp_like_condition::=3D
Description of regexp_like_condition.gif follows Description of the =
illustration regexp_like_condition.gif
*
source_string is a character expression that serves as the search =
value. It is commonly a character column and can be of any of the =
datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
It's described in detail in Jonathan's book and 10g manuals.
--
Mladen Gogala
A & E TV Network
Ext. 1216
> -----Original Message-----
> From: MacGregor, Ian A. [mailto:ian@xxxxxxxxxxxxxxxxx]
> Sent: Friday, September 17, 2004 10:09 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: OWA Pattern Match
>=20
>=20
> One of our developers is trying to use the following regular =3D=20
> expression.
>=20
> ^[a-z]+(\-[a-z]+)*\/\d{7,9}$
>=20
> If we test it with a parser outside of Oracle it performs as=20
> expected.
>=20
> Regular expression: ^[a-z]+(\-[a-z]+)*\/\d{7,9}$
> Input text: hep-ex/0408086
> Match!
>=20
> Input text: qwerty\876654 No Match!
>=20
> But inside of Oracle OWA_PATTERN.MATCH complains the expression is =3D =
> illegal in Oracle 9, and crashes in Oracle 8.
> Here's an anonymous block =3D function, and the results from 9i. This =
> function should return null as =3D there is a match, but if not it=20
> should suffer a divide by 0
> error. =3D20
>=20
> SQL> set echo on
> SQL> -- FUNCTION MATCH RETURNS BOOLEAN
> SQL> -- Argument Name Type =20
> In/Out =3D
> Default?
> SQL> -- ------------------------------
> ----------------------- ------ =3D
> --------
> SQL> -- LINE VARCHAR2 IN
> SQL> -- PAT VARCHAR2 IN
> SQL> -- FLAGS VARCHAR2 =20
> IN =3D
> DEFAULT
> SQL> --
> SQL> -- this one assumes that the parameters are Line followed by=3D20 =
> SQL> Pattern declare
> 2 preprintregex VARCHAR2(30) :=3D3D=20
> '^[a-z]+(\-[a-z]+)*\/\d{7,9}$';
> 3 i integer :=3D3D 0;
> 4 begin
> 5 IF Sys.Owa_Pattern.Match('hep-ex/0408086', preprintregex) THEN
> 6 null;
> 7 else
> 8 i :=3D3D 1/i;
> 9 end if;
> 10 end;
> 11 .
> SQL> /
> declare
> *
> ERROR at line 1:
> ORA-20001: In omatch: illegal pattern found
> ORA-06512: at "SYS.OWA_PATTERN", line 766
> ORA-06512: at "SYS.OWA_PATTERN", line 869
> ORA-06512: at "SYS.OWA_PATTERN", line 966
> ORA-06512: at "SYS.OWA_PATTERN", line 989
> ORA-06512: at "SYS.OWA_PATTERN", line 998
> ORA-06512: at line 5
>=20
> So what's wrong with the pattern?.
>=20
> Here's the breakdonw of the regular expression
>=20
> -- start of line ^
> -- followed by 1 or more letters [a-z]+
> -- optionally followed by... (
> -- a dash (-) and \-
> -- one or more letters [a-z]+
> -- repeat this group as necessary )*
> -- followed by a slash (/) \/
> -- followed by 7 to 9 digits \d{7,9}
> -- end of line $
>=20
> --
> //www.freelists.org/webpage/oracle-l
>=20
--
//www.freelists.org/webpage/oracle-l
Other related posts:
