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