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%');
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%');
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 (See ORA-01722.ora-code.com) 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
