From oracle-l-bounce@freelists.org Wed Apr 20 16:21:31 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j3KLLVhk014699 for ; Wed, 20 Apr 2005 16:21:31 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j3KLLV4Z014694 for ; Wed, 20 Apr 2005 16:21:31 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DACC21857F3; Wed, 20 Apr 2005 15:19:11 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 27224-08; Wed, 20 Apr 2005 15:19:11 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 574CC185CC0; Wed, 20 Apr 2005 15:19:11 -0500 (EST) Priority: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Subject: RE: Regular Expression confusion Date: Wed, 20 Apr 2005 14:18:48 -0600 Message-ID: <17CAB0BF27BCFC47B0E4554A0E2F962B4395AD@fiji.arraybp.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Regular Expression confusion Thread-Index: AcVF5bGDK9Gu5RUVSyWzUTZl9tj3bgAAF6Dw From: "Reidy, Ron" To: , X-OriginalArrivalTime: 20 Apr 2005 20:15:26.0593 (UTC) FILETIME=[B0CC3F10:01C545E5] X-archive-position: 18690 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Ron.Reidy@arraybiopharma.com Precedence: normal Reply-To: Ron.Reidy@arraybiopharma.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.1 required=5.0 tests=AWL,LINES_OF_YELLING, LINES_OF_YELLING_2 autolearn=no version=2.63 Can you say something like "upper(regex_replace(name, '[:punct;]' ..."? ----------------- Ron Reidy Lead DBA Array BioPharma, Inc. 303.386.1480 -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of William B Ferguson Sent: Wednesday, April 20, 2005 2:13 PM To: oracle-l@freelists.org Subject: Regular Expression confusion I'm getting confused on regular expressions (Oracle 10.1.0.4). I want to strip all punctuation from a user supplied input string except for the = '%' wildcard character. To see if my formatting of the syntax was correct, I wrote the following sql statement: SQL> set escape off SQL> select UPPER(regexp_replace(name,'[''''|"|;| |-|,|.]')) test_data 2 from names 3 where upper(regexp_replace(name,'[[:punct:][:space:]]')) 4 like ('%JGO%'); TEST_DATA ---------------------------------------------------------------------- A-JGOLDMININGCO AJGOLDMINE AJGOLDMININGCO AJGOLDMININGCO AJGOLDMININGCO EJGOGGINSPROSPECT JJGORDONQUARRY 7 rows selected. Not quite what I wanted, as a hyphen still appears. So, I tried escaping the hyphen: SQL> select UPPER(regexp_replace(name,'[''''|"|;| |\-|,|.]')) test_data 2 from names 3 where upper(regexp_replace(name,'[[:punct:][:space:]]')) 4 like ('%JGO%'); TEST_DATA ---------------------------------------------------------------------- A-JGMC AJGM AJGMC AJGMC AGMC EJGP JGQ 7 rows selected. Definitely not what I wanted! So, I figured I'd try adding an extra set = of brackets (like the second regexp_replace uses): SQL> select UPPER(regexp_replace(name,'[[''''|"|;| |\-|,|.]]'))=20 SQL> test_data 2 from names 3 where upper(regexp_replace(name,'[[:punct:][:space:]]')) 4 like ('%JGO%'); TEST_DATA ---------------------------------------------------------------------- A-J GOLD MINING CO A. J. GOLD MINE A. J. GOLD MINING CO. A.J. GOLD MINING CO. AJ GOLD MINING CO. E J GOGGINS PROSPECT JJ GORDON QUARRY 7 rows selected. That had the effect of negating what I wanted to accomplish in the first place! I might as well have just selected name. And if I changed the first regexp_replace to UPPER(regexp_replace(name,'[''''|"|;| |'-'|,|.]')), Then I get an ORA-01722 invalid number. How do I modify my first regexp_replace to also strip out the hyphen = while allowing the '%' wildcard, unlike the :punct: class? ------------------------------------------------------------ Bill Ferguson U.S. Geological Survey - Minerals Information Team PO Box 25046, MS-750 Denver, Colorado 80225 Voice (303)236-8747 ext. 321 Fax (303)236-4208 -- http://www.freelists.org/webpage/oracle-l This electronic message transmission is a PRIVATE communication which = contains information which may be confidential or privileged. The information is = intended=20 to be for the use of the individual or entity named above. If you are = not the=20 intended recipient, please be aware that any disclosure, copying, = distribution=20 or use of the contents of this information is prohibited. Please notify = the sender of the delivery error by replying to this message, or notify us = by telephone (877-633-2436, ext. 0), and then delete it from your system. -- http://www.freelists.org/webpage/oracle-l