Re: replace single char for string using regexp_replace

PegoraroF10 wrote:

> I have a
> Replace(Replace(Replace(Replace($$Text,with;On'It"$$,',','chr(59)'),';','chr(44)'),'"','chr(34)'),'''','chr(39)')
> It works but I would like to call just one Replace.

For performance, it might be the fastest method, despite the
lack of elegance and the multiple passes on the string.
In a recent discussion on the list [1] I've asked on how best to do
multiple string replacements. That's a generalized version of your
question and the answer might be heavier but anyway here's some
code on the wiki with plperl and plpgsql versions:

https://wiki.postgresql.org/wiki/Multi_Replace_Perl
https://wiki.postgresql.org/wiki/Multi_Replace_plpgsql

If you're okay with plperl see the first version, as it's
much more efficient, with Perl implementing the multiple
replacement natively in its regexp engine (plus implementing
the search with a trie since 5.10).

The plpgsql version tries do its best with regexp_replace, but it
scales much worse as the number of replacements grows.
But it does work in the situations where a stack of nested replace() calls
wouldn't work, like replace foo with bar and bar with foo.

In your case, as the strings to replace always consist only of one character,
you might also split the string by characters, replace them with a
CASE WHEN... construct, and reassemble the result with string_agg,
as in:

select string_agg(c, '') from
(select case c
when ',' then 'chr(59)'
when ';' then 'chr(44)'
... other substitutions...
else c
end
from
regexp_split_to_table($$The Original String$$, '') as s1(c)
) as s2(c);

If the strings are not too large and there are many occurrences of the
characters
to replace, I would expect this to be more efficient than the more generic
plpgsql-based solution above. Against the Perl version I don't know.
There is a per-call overhead with plperl that can't be ignored if you're
focused on performance.

[1]
https://www.postgresql.org/message-id/306b726b-f185-4668-bffe-ac8e7f78878e@manitou-mail.org

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite