RE: Regular Expression confusion
- From: William B Ferguson <wbfergus@xxxxxxxx>
- To: Ron.Reidy@xxxxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 20 Apr 2005 14:22:02 -0600
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@xxxxxxxxxxxxxxxxxx]
Sent: Wednesday, April 20, 2005 2:19 PM
To: William B Ferguson; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of William B Ferguson
Sent: Wednesday, April 20, 2005 2:13 PM
To: oracle-l@xxxxxxxxxxxxx
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
--
//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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
