Re: workaround for lack of REPLACE() function

Tom Lane wrote:
> "Agent155 Support" <matt(at)planetnet(dot)com> writes:
>
>>What do folks usually do when they have to do a global search/replace on a =
>>big table?
>
>
> You can code pretty much any text transformation you'd like in plperl or
> pltcl, both of which languages are very strong on string manipulations.
> So there's not been a lot of concern about the lack of a SQL-level
> substitution operator.
>
> IIRC, SQL99 does specify some sort of substring replacement function,
> and Thomas recently implemented it for 7.3. But it's not very bright
> and I suspect people will keep falling back on plperl or pltcl to do
> anything nontrivial.
>

I think Thomas did just recently commit the SQL99 OVERLAY function, but
similar to Tom's comment, I don't like the way SQL99 defines it. I've
written a replace() C function (along with a couple of other string
manipulation functions) for my own use. If you'd like a copy let me know
and I'll gladly send it to you.

I have thought about sending it in as a contrib, but wasn't sure if
there was enough interest to warrant it.

Joe