Discussions
Categories
- 381.9K All Categories
- 2.1K Data
- 207 Big Data Appliance
- 1.9K Data Science
- 447.4K Databases
- 220.8K General Database Discussions
- 3.7K Java and JavaScript in the Database
- 23 Multilingual Engine
- 516 MySQL Community Space
- 463 NoSQL Database
- 7.8K Oracle Database Express Edition (XE)
- 2.9K ORDS, SODA & JSON in the Database
- 469 SQLcl
- 3.9K SQL Developer Data Modeler
- 185.9K SQL & PL/SQL
- 20.9K SQL Developer
- 292.6K Development
- 7 Developer Projects
- 124 Programming Languages
- 289.4K Development Tools
- 95 DevOps
- 3K QA/Testing
- 645.5K Java
- 23 Java Learning Subscription
- 36.9K Database Connectivity
- 150 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.8K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 16 Java Essentials
- 143 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 197 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 242 LiveLabs
- 35 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 166 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 225 Portuguese
Equivalents To SQL Server's STUFF() Function
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!
Best 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;
Answers
-
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.
-
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;
-
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.
