I don't know if anyone has a similar function, so this is just a tip.
We have a global function "f_str_replace()", which uses a loop with the powerbuilder Pos() and Replace() functions to replace all occurrences of an "old" piece of string within a string and replaces them with a "new" piece of string.
Now that - with pb2019 r2 - it's way easier to call a c# function in a DLL, I decided to replace that function with the Replace() of C#. It no longer needs a loop to find and replace all "old" strings. It's a single call to Replace() in .Net.
This makes the replace between 500 and 600 times faster on my machine. (this is very much depending on how big the strings are and what you might be replacing with what).
We use this replace function in lots of places, so it's pretty useful.
We instantiate the .Net caller object in a global variable, so it only has to be done once.
(If anyone's interested and needs source code, just let me know).
BTW: I tried to see if .net's Substring() function is also faster than Powerbuilder's Mid() function, but that was a big disappointment. Powerbuilder's Mid() function is waaaaay faster than the .net Substring() function. Probably due to all kinds of checks with exceptions that .net does before actually performing the substring().
It's an interesting point! I have used (for example) Oracle's Replace / regexp_replace function many times, but only to replace existing data in a database, which I think is pretty fast!
The (search and) replace that I'm thinking of with all of this, is more generic: change contents of files read from disk, dwSyntax, etc.
. You could try to do something like "SELECT Replace(:ls_myVar, ...) .... FROM DUAL;", maybe using dynamic SQL, but it's going to be up to a limited size before needing to work with a Blob variable. Once you need UPDATEBLOB / SELECTBLOB things would slow down considerably.
Also when working with a DB Replace, you're working over a network connection to your database (unlees you run it locally on your own PC). That would also slow down the process. So, it depends on what you're doing I suppose.