Regular Expression confusion

From: William B Ferguson <wbfergus_at_usgs.gov>
Date: Wed, 20 Apr 2005 14:12:33 -0600
Message-ID: <OF295559A7.559D7E2B-ON06256FE9.006D2CD4@usgs.gov>

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 Received on Wed Apr 20 2005 - 16:15:47 CDT