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@xxxxxxxxxxxxxxxx Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite
