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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Regular Expression confusion

RE: Regular Expression confusion

From: William B Ferguson <wbfergus_at_usgs.gov>
Date: Wed, 20 Apr 2005 14:22:02 -0600
Message-ID: <OF4DAB709E.2EBE761D-ON06256FE9.006FBEA4@usgs.gov>


Ron,

I tried that before in my application, and it strips out the '%' (wildcard) operator that I need to allow for, as valid user input.



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

-----Original Message-----

From: Reidy, Ron [mailto:Ron.Reidy_at_arraybiopharma.com] Sent: Wednesday, April 20, 2005 2:19 PM
To: William B Ferguson; oracle-l_at_freelists.org Subject: RE: Regular Expression confusion

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of William B Ferguson Sent: Wednesday, April 20, 2005 2:13 PM
To: oracle-l_at_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,'[[''''|"|;| |\-|,|.]]')) 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 to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution 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 Received on Wed Apr 20 2005 - 16:25:10 CDT

Original text of this message

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