Re: [GENERAL] workaround for lack of REPLACE() function

Thomas Lockhart wrote:
> (crossposted to -hackers, should follow up on that list)

<snip>

> OK, this is in the "can't do it what we have" category. Should we have
> it accept a regular expression rather than a simple string? In either
> case it should probably go into the main distro. Except that I see
> "REPLACE" is mentioned as a reserved word in SQL99. But has no other
> mention in my copy of the draft standard. Anyone else have an idea what
> it might be used for in the standard?

Not sure, but I see what you mean. Perhaps because of Oracle pushing to
legitimize the "CREATE OR REPLACE" syntax? In any case, this works in 8i:

SQL> select replace('hello','l','x') from dual;

REPLACE('HELLO','L','X')
------------------------
hexxo

and here it is in MSSQL 7:

select replace('hello','l','x')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

hexxo

(1 row(s) affected)

and my proposed PostgreSQL function:

test=# select replace('hello','l','x');
replace
---------
hexxo
(1 row)

so at least we would be consistant/compatable with these two.

>
> The other functions look useful too, unless to_char() and varbit can be
> evolved to support this functionality.

I will take a look at merging these into existing functions, but I have
a few other things ahead of this in my queue.

One of the reasons I wasn't pushing too hard to get replace() into the
backend is because my current solution is a bit of a hack. It uses the
builtin length, strpos and substr text functions (which I think makes
sense since they already know how to deal with mb strings), but because
they accept and return text, I'm doing lots of conversions back and
forth from (* text) to (* char). To do this "right" probably means
reworking the text string manipulation functions to be wrappers around
some equivalent functions accepting and returning C strings. That was
more work than I had time for when I wrote the current replace(). But as
I said, if there is support for getting this into the backend, I'll add
it to my todo list:

- Create new backend function replace()
- Either create new backend functions, or merge into existing functions:
to_hex() and extract_tok()

Joe