Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

how to use onle only one repalce function instead of nested replace function?

Posted on 2014-01-23
15
293 Views
Last Modified: 2014-01-26
Hi Expert,

I am using this below   3 replace function to replace 3 characters.
can i do only  in one replace function or any other function.

select replace(replace(replace(facility_name, '\', '\\\\'), '''', '\'''), '"', '\\"') from facility
 
 Thanks
 Thomos
0
Comment
Question by:deve_thomos
  • 6
  • 5
  • 2
  • +1
15 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39805065
Other than "you just want to", what is the real need to do this?

The execution time of a single replace is pretty small.

You 'might' be able to do it with regular expressions (but I don't see how right now) but those types of function calls are expensive and three normal ones will probably be faster.
0
 

Author Comment

by:deve_thomos
ID: 39805085
Hello Expert ,
how i will do with regular expression??
can you please tell me ?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39805108
>>how i will do with regular expression??

As I said, I don't know if you can.  That is why I said 'might' be able to.

Off the top of my head I can't think of the expression to do what you want in a single call.

Even if an Expert here comes up with one, why would you want to see it it there is a strong possibility it will be a lot slower?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 74

Expert Comment

by:sdstuber
ID: 39805121
you can't do it with regular expressions either, at least not with Oracle's versions of them.
You'd have to use 3 expressions which, would be worse than what you have.

With a more sophisticated regexp parser, such as those in php and perl, it might be possible; but again, at the expense of performance.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39805126
if you're worried simply about the cosmetic aspect of having 3 function calls, you could always write your own function that performed the 3 replaces for you but only exposed one function call.

That's obviously worse; but cosmetic reason is the only reason I can think of even wanting to try.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39805185
I can't get everything into a single regexp_replace but I can combine the quotes and get it down to two replaces, one regular and one regular expression.

Below is a test case that tests the two.

Pay particular attention the the extreme number of the loop count.  I'm running this on my home PC and had to use such a large number to get something to show a difference.

The entire test case on my PC is 7 seconds.

100 Million executions on a standard PC and they are still both close in execution times...  Do you really need to remove the three replace calls?

declare
	facility_name varchar2(100) := 'abc\def''ghi"jkl';
	new_str varchar2(100);
	start_time timestamp;
begin

start_time := systimestamp;
for i in 1..100000000 loop
	new_str := replace( replace( replace( facility_name, '\', '\\\\'), '''', '\'''), '"', '\\"');
end loop;

dbms_output.put_line(systimestamp - start_time);


start_time := systimestamp;
for i in 1..100000000 loop
	new_str := regexp_replace(replace(facility_name,'\','\\\\'), '([''"])','\\\1' );
end loop;
dbms_output.put_line(systimestamp - start_time);


end;
/

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39805396
I don't get the correct results with the regexp


SELECT REGEXP_REPLACE(REPLACE(facility_name, '\', '\\\\'), '([''"])', '\\\1') regrep,
       REPLACE(REPLACE(REPLACE(facility_name, '\', '\\\\'), '''', '\'''), '"', '\\"') rep3
  FROM (SELECT 'abc\def''ghi"jkl' facility_name FROM DUAL);


abc\\\\def\'ghi\"jkl   -- regexp
vs
abc\\\\def\'ghi\\"jkl   -- rep3


the regexp is dropping a slash
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39805444
deve_thomos,
Please confirm that a single quote is replaced with \' and a double quote is replace with \\".

This doesn't seem correct to me when escaping certain characters.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39805459
If you're wondering if "just one extra slash" makes a difference - YES!

The reason 3 expressions are needed is because there is no consistent pattern between your 3 inputs and your 3 outputs.

If you change the rules of patterns by just one character it's a whole new problem.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39805501
Even if my test case with only two replaces was a little off, it still confirms my original post in http:#a39805065 

What you have is probably the fastest way to get what you want.

It looks like there really is no other way given those specific requirements and even if there was, it wouldn't be 'better'.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39805505
>>> What you have is probably the fastest way to get what you want.

agreed, and, in my opinion, the easiest to read and understand.  

You have 3 things to replace, so you replace 3 times.  It's quite logical as-is, as well as being the most efficient.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39806595
Can you provide some sample data and what your expected output would be?
0
 
LVL 32

Expert Comment

by:awking00
ID: 39806638
Let me first be sure of what you're trying to do. It looks like you want to replace every backslash with four backslashes, replace every single quote with a backslash and a single quote, and replace every double quote with two backslashes and a double quote. Is that correct?
0
 

Author Comment

by:deve_thomos
ID: 39810282
Yes ...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39810685
>>Yes ...

I believe this has been answered:  three replaces is likely the best method to do this.

You haven't posted back why you do not want to do it this way.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle query output question 4 47
oracle 11g 23 106
exp/imp 25 85
Error in creating a view. 8 22
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question