SQL & PL/SQL

INSTR() and SUBSTR() would work just fine edit: DOH, what was I thinking?!?! Saw Frank's reply and realized that I typed without thinking. @"Frank Kulash", we also need ||

How it would work is left as an exercise for the reader.

Hi,

To get the same results in Oracle, SUBSTR is the only function you need.  In general, you'll have to call that function twice: once to get the part of the original string before start, and once to get the part after (start + length).

If you really, really wanted to minimize the number of function calls, you can do it with a single call to REGEXP_REPLACE, but it would require more || operations.  Since the || operrator does the same thing as the CONCAT function, I assume you'd want to minimize them, too.

Or it could be done using a single regexp_replace function, but again, if this isn't a question about an issue, I'll leave it up to those who want to have the exercise.

Hi,

John Stegeman wrote:

Frank Kulash, we also need || 
...

|| is an operator, not a function.

As OP phrased it, this sounds like a question to test how clever you are and how well you know Oracle SQL, regardless of any practical uses.

Of course, OP may have phrased the question poorly, and may actually want a simple and/or efficient way, regardless of what tools and techniques are used.

Hey guys.

To clear up some ambiguity here: I'm looking for the least processing intensive drop-in replacement for the STUFF() method. Paulzip provided exactly what I was looking for, bravo!

Is anyone willing to contribute the regex solution? I'd like to compare the explain plan of both routines. Please wrap the regex solution in a function called STUFF() like Paulzip did.

This may seem like a trivial question to some of you veterans, but it's important to me that this function is optimal, as I'll be nesting calls to this in queries expected to return thousands of rows.

Here's Paulzip's plan, explained:

-----------------------------------------------------------------

| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |

-----------------------------------------------------------------

|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |

|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------

regexp will NOT be the one you want. Guaranteed.

Oh, and by the way - your execution plan has nothing to do with the function. I guess you did "select stuf(....) from dual" You'll get the same execution plan with a stuff() function written to use regexp.

Yeah?

Although it wasn't a verbosely explained plan, I thought that the statistics were inclusive of the function call. Am I wrong?

Should I execute plan the statement instead?

Hi,

John Stegeman wrote:

... your execution plan has nothing to do with the function. I guess you did "select stuf(....) from dual" You'll get the same execution plan with a stuff() function written to use regexp.

Good point!

OP: the execution plan describes how rows are fetched.  In most cases, how the rows are fetched has much more to do with efficiency than what you do with the rows after you get them.  However, what you do with the rows after you get them can be significant.

Imagine you want to travel to another country, and have lunch after you get there.  If you're concerned about cost, you'll focus on how you get to the other country: a first-class plane ticket might cost a lot more than a second-class train ticket.  Where you have lunch and what you eat will make a difference, too, but probably not nearly as much.

Explain plan is not going to be useful in evaluating the performance of a function (as long as the function isn't itself doing a bunch of SQL).

If you wanted to measure performance, I'd be doing something like using v$sesstat snapshots before and after executing the function a few (dozens, hundreds, thousands, depending on the function) times and seeing how much CPU is used and also measuring the elapsed times.

Is anyone willing to contribute the regex solution?

For your own learning, you should try to build the regexp version.  You can then post it here to get feedback.

John Stegeman wrote:

regexp will NOT be the one you want. Guaranteed.

Maybe... maybe not.

We know regexp functions are more CPU intensive in general, but the payoff between a single fairly simple regexp compared to several regular string functions may be ok... it depends on what you're doing.  Certainly if it's just for a small set of data, the chances are you won't notice any real difference.

More fuction calls might provide more not null results.

Replacing nvl(p_start,0) with case when p_start is null then 0 else p_start end and accordingly might be faster.

with

data as

(select '123456789' p_expr,3 p_start,5 p_length,'***' p_stuff from dual union all

select null,null,null,null from dual union all

select null,null,null,'123456789' from dual union all

select null,0,0,'123456789' from dual union all

select null,0,3,'123456789' from dual union all

select null,3,0,'123456789' from dual union all

select '+~+~+~+~',0,0,'123456789' from dual union all

select '+~+~+~+~',3,0,'123456789' from dual union all

select '+~+~+~+~',0,3,'123456789' from dual union all

select 'ABCDEFGH',3,11,'abcdefgh' from dual union all

select 'ABCDEFGH',3,3,'abcdefgh' from dual union all

select 'ABCDEFGH',3,3,null from dual union all

select 'ABCDEFGHIJK',-5,3,'!!!' from dual union all

select 'ABCDEFGHIJK',-5,-3,'!!!' from dual union all

select 'ABCDEFGHIJK',5,-3,'!!!' from dual union all

select '987654321',null,null,'123456789' from dual union all

select '987654321',null,null,null from dual

)

select p_expr,p_start,p_length,p_stuff,

       case when p_start is not null

             and p_length is not null

             and p_start > 0

             and p_start < length(p_expr)

            then substr(p_expr,1,p_start - 1)||p_stuff||substr(p_expr,p_start + p_length)

           else null

         end result,

         substr(p_expr,1,nvl(p_start,0) - 1)||p_stuff||substr(p_expr,nvl(p_start,0) + nvl(p_length,0)) variant

  from data

P_EXPR	P_START	P_LENGTH	P_STUFF	RESULT	VARIANT
123456789	3	5	***	12***89	12***89
-	-	-	-	-	-
-	-	-	123456789	-	123456789
-	0	0	123456789	-	123456789
-	0	3	123456789	-	123456789
-	3	0	123456789	-	123456789
+~+~+~+~	0	0	123456789	-	123456789+~+~+~+~
+~+~+~+~	3	0	123456789	+~123456789+~+~+~	+~123456789+~+~+~
+~+~+~+~	0	3	123456789	-	123456789+~+~+~
ABCDEFGH	3	11	abcdefgh	ABabcdefgh	ABabcdefgh
ABCDEFGH	3	3	abcdefgh	ABabcdefghFGH	ABabcdefghFGH
ABCDEFGH	3	3	-	ABFGH	ABFGH
ABCDEFGHIJK	-5	3	!!!	-	!!!JK
ABCDEFGHIJK	-5	-3	!!!	-	!!!DEFGHIJK
2 people found this helpful John Stegeman Jul 28 2016 Never trust, always verify At any rate, substr and concatenation are pretty simple operations, so I still believe it will be faster than regexp, Blu's point notwithstanding @"BluShadow" what about the overhead of having to parse/compile the regular expression itself? My "gut feel" is that just that part of the work will take more resource than the simple substr/|| 2 people found this helpful Paulzip Jul 28 2016 A regular expression solution will be slower, I can pretty much guarantee that. I forgot to add deterministic clause to my function which would allow result caching, so would be faster if the same inputs occurred. I'd be very surprised if you could create something faster, as any solution would still need to use substr. 2 people found this helpful John Stegeman Jul 28 2016 and FYI: Google found the same function Ray Winkelman Jul 28 2016 I love when an actually smart person contributes useful information in a place you would expect smart people to contribute useful information. You rock, Etbin. BluShadow Jul 28 2016 regexp is certainly likely to be slower in most cases, but for the purpose of illustrating (I don't have a database to hand to test at the minute)... It would be something like: regexp_replace(source_string, '^(.{4}).{3}(.*)$', '\1STUFFSTRING\2') So, in this example, the "4" is the number of characters to keep on the left The "3" is the number of characters to discard and the "STUFFSTRING" is the string to put in place of the 3 characters removed. Whilst regexp are generally considered CPU intensive, this pattern is relatively straightforward. The first 4 character are easy to parse,  The next 3 characters are easy to parse and the remainder of the string are easy to parse. The replacement is just including the first 4 character and the remainder of the string around the string you want to include in place of the 3 that are removed. The use of "^" to indicate the start of the string and the "$" to indicate the end of the string will help with parsing as it provides definite anchor points for the parsing to consider. Certainly, other regular expression patterns can be more complex and more CPU intensive, especially if they include "|" (or), and/or variable string length patterns etc. The best way to determine what's more performant between regexp and regular string functions would be to test it on a good volume of data. 1 person found this helpful BluShadow Jul 28 2016 John Stegeman wrote: BluShadow what about the overhead of having to parse/compile the regular expression itself? My "gut feel" is that just that part of the work will take more resource than the simple substr/|| Overhead of parsing, whether it's code or string parsing, always depends on the complexity of what you're parsing.  Some things are simple and parsed quickly, other things are complex and slow. In this 'challenge' the obtaining of a fixed set of characters from the left, followed by a fixed set of characters to discard, followed by the remaining characters is not complex.  Sure, SUBSTR can use relative byte positioning rather than having to do much in the way of parsing, but the difference I suspect will be negligible unless we're processing large volumes of data. Paulzip Jul 28 2016 — edited on Jul 28 2016 Etbin wrote: More fuction calls might provide more not null results. Replacing nvl(p_start,0) with case when p_start is null then 0 else p_start end and accordingly might be faster. Regards Etbin Mine might be more nulls, but it matches the SQL Server notes on the operation and parameters of Stuff function! 1 person found this helpful Etbin Jul 28 2016 Until now I was only aware SQL Server exists and most probably such Stuff function parameters specifications (actually limitations) and notes on operation are just another reason to let the before mentioned awareness remain the same. Might not be irrelevant to the OP though, but at least in principle, migrating from SQL Server to Oracle requiring Oracle to work according to SQL Server specifications sounds weird (at least to me). A kind of modify-everything-without-changing-anything. Regards Etbin
1 - 22