Forum Stats

  • 3,770,351 Users
  • 2,253,099 Discussions
  • 7,875,413 Comments

Discussions

Equivalents To SQL Server's STUFF() Function

Ray Winkelman
Ray Winkelman Member Posts: 75 Green Ribbon
edited Jul 28, 2016 5:16PM in SQL & PL/SQL

Hey Guys!

Let's have some fun with strings today.

So, I've got some T-SQL code I'm translating into PL-SQL, and I've been encountering some uses of SQL Server's STUFF() function. Microsoft's documentation on the STUFF() function states:

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

The header of the STUFF() function accepts the following arguments:

STUFF ( character_expression , start , length , replaceWith_expression )


So, considering the following code as our example, how can we manipulate a string in PL-SQL with as few calls to system functions as possible? Let's aim for the most performant solution - as always.

select stuff( 
( 
    select '123456789' 
    from   table_name for clob path('')
), 1, 4, ''
) as example 
from dual;

And, GO!

John StegemanFrank KulashRay WinkelmanEtbinAndrewSayerJonathan HultUser_D1JQ2

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond
    edited Jul 28, 2016 1:05PM Accepted Answer

    create or replace function Stuff(pExpr varchar2,

                                     pStart integer,

                                     pLength integer,

                                     pReplace varchar2) return varchar2 is

    begin

      return case

               when pStart is not null and pLength is not null and

                    pStart > 0 and pStart < length(pExpr) then

                 substr(pExpr, 1, pStart - 1)||pReplace||substr(pExpr, pStart + pLength)

               else null

             end; 

    end;

    Ray WinkelmanJonathan Hult
«13

Answers

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited Jul 28, 2016 1:01PM

    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.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    edited Jul 28, 2016 1:03PM

    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.

    John Stegeman
  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond
    edited Jul 28, 2016 1:05PM Accepted Answer

    create or replace function Stuff(pExpr varchar2,

                                     pStart integer,

                                     pLength integer,

                                     pReplace varchar2) return varchar2 is

    begin

      return case

               when pStart is not null and pLength is not null and

                    pStart > 0 and pStart < length(pExpr) then

                 substr(pExpr, 1, pStart - 1)||pReplace||substr(pExpr, pStart + pLength)

               else null

             end; 

    end;

    Ray WinkelmanJonathan Hult
  • BluShadow
    BluShadow Member, Moderator Posts: 41,496 Red Diamond
    edited Jul 28, 2016 1:04PM

    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.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    edited Jul 28, 2016 1:17PM

    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.

    John Stegeman
  • Ray Winkelman
    Ray Winkelman Member Posts: 75 Green Ribbon
    edited Jul 28, 2016 1:30PM

    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 |

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

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited Jul 28, 2016 1:45PM

    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.

    Frank KulashRay WinkelmanAndrewSayerRay Winkelman
  • Ray Winkelman
    Ray Winkelman Member Posts: 75 Green Ribbon
    edited Jul 28, 2016 1:58PM

    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?

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    edited Jul 28, 2016 1:59PM

    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.

    Ray WinkelmanRay Winkelman
  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited Jul 28, 2016 2:02PM

    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.

    Ray WinkelmanRay Winkelman
This discussion has been closed.