/*
Substitute substrings within a larger string, with Perl s// operator,
in a single pass. Each element in @orig found in @string (scanned left
to right) is replaced by the element at the same index in @repl.
When multiple strings in the array match simultaneously, the longest one
wins.
*/
CREATE OR REPLACE FUNCTION multi_replace(string text, orig text[], repl text[])
RETURNS text
AS $BODY$
my ($string, $orig, $repl) = @_;
my %subs;
# Check that the arrays are of the same size, unidimensional,
# and contain no null values.
if (@$orig != @$repl) {
elog(ERROR, "array sizes mismatch");
}
if (ref @$orig[0] eq 'ARRAY' || ref @$repl[0] eq 'ARRAY') {
elog(ERROR, "multi-dimensional arrays are not allowed");
}
if (grep { !defined } (@$orig, @$repl)) {
elog(ERROR, "null elements are not allowed");
}
# Each element of $orig is a key in %subs to the element at the same
# index in $repl
@subs{@$orig} = @$repl;
# Build a regexp of the form (s1|s2|...)
# with the substrings sorted to match longest first
my $re = join "|", map quotemeta,
sort { (length($b) <=> length($a)) } keys %subs;
$re = qr/($re)/;
# The order will be kept in matching because (from perlre):
# "Alternatives are tried from left to right, so the first alternative
# found for which the entire expression matches, is the one that is
# chosen"
$string =~ s/$re/$subs{$1}/g;
return $string;
$BODY$ language plperl strict immutable;
2条答案
按热度按时间omvjsjqw1#
要获得如上所述的所需输出,您可以使用以下查询来解决它。查询如下:
字符串
示例说明和实现可以在这里找到:https://dbfiddle.uk/93OllCaa
希望这有帮助
4smxwvx52#
从Postgres 16开始,没有内置函数可以在一次遍历中执行多个替换。Perl的替换操作符提供了一种有效的方法。我使用下面的
plperl
函数:字符串
范例:
型
您可以在博客文章Multiple strings replacement with plperl中找到有关问题陈述和实现的更多细节。