RE: Regular Expression confusion
- From: "Reidy, Ron" <Ron.Reidy@xxxxxxxxxxxxxxxxxx>
- To: <wbfergus@xxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 20 Apr 2005 14:18:48 -0600
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,'[[''''|"|;| |\-|,|.]]'))=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
--
//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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
