OraFAQ Forum: SQL & PL/SQL » REGEXP

Home » SQL & PL/SQL » SQL & PL/SQL » REGEXP_REPLACE (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

REGEXP_REPLACE [message #656437]

Wed, 05 October 2016 23:30

chavva.kiru@gmail.com
Messages: 23
Registered: April 2012
Location: hyderabad

Junior Member

Hi,

Im Using In My Merge Stmt Regexp_Replace('Bankname','[^a-zA-Z0-9chr(32)-chr(46)\S]','')

for example:select Regexp_Replace('STATE BANK & ASSOCIATES','[^a-zA-Z0-9chr(32)-chr(46)\S]','') from dual
O/p:STATE BANK ASSOCIATES
chr(32) to chr(46) Are not Working.

I changed the Query As below By Substituting Chr(32) to chr(46)

for example:select Regexp_Replace('STATE BANK & ASSOCIATES','[^a-zA-Z0-9!"$%&()*+-.\S]','') from dual

Its Working fine.But chr(39) which is ' not working in above.How to include it or how to rewrite the query by including chr(39),Please help

[Updated on: Wed, 05 October 2016 23:35]

Report message to a moderator

Re: REGEXP_REPLACE [message #656439 is a reply to message #656437]

Thu, 06 October 2016 00:28

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> select q'(AB'CD)' ori, regexp_replace(q'(AB'CD)','['']') res from dual;
ORI   RES
----- ----
AB'CD ABCD

That say, it is better to use TRANSLATE for something like this.

Generally speaking, it is better to explain what you want to do and post what does not work rather than what it works but does not do what you want. Don't you think it'd be clearer?

Also, please read How to use [code] tags and make your code easier to read and post your version with 4 decimals.

Report message to a moderator

Re: REGEXP_REPLACE [message #656440 is a reply to message #656439]

Thu, 06 October 2016 00:30

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Mon, 19 January 2015 07:22

From your previous topic:

Michel Cadot wrote on Thu, 16 August 2012 16:20
From your previous topics:

BlackSwan wrote on Wed, 11 April 2012 16:43
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
BlackSwan wrote on Wed, 11 April 2012 20:29
...
READ & FOLLOW the Posting Guidelines! http://www.orafaq.com/forum/t/88153/0/

With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

In addition, Barbara helped you so much in your previous topics providing you codes and examples and you did never feedback and thank her.
Are you the kind of parasite we saw too much in these days?
Or are you a valuable person who deserves to be helped?

Report message to a moderator

Previous Topic:	Get the deletion date for a certain DB object (Stored Procedure)
Next Topic:	view and Cursor behavior :Run time Exception:ORA-00932: inconsistent datatypes: expected - got -

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 15:30:42 CDT 2024