Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Regular Expression confusion
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.
-----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;]' ..."?
-----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
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
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 MINE A. J. GOLD MINING CO. A.J. GOLD MINING CO.
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?
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
![]() |
![]() |