원본출처 : http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=23
Submitted on: 03-16-2001
Description:
mimic oracle's replace function. versions in pltcl and plpgsql.
Code:
-- by Jonathan Ellis (jbellis@hotmail.com)
-- licensed under the GPL
-- emailing me improvements is appreciated but not required
-- args: string substring replacement_substring
create function replace (varchar, varchar, varchar) returns varchar as '
-- escape out characters that regsub would treat as special
regsub -all {&} "$3" {\\\&} 3
regsub -all {\\[0-9]} "$3" {\\\0} 3
eval "regsub -all \{$2\} \{$1\} \{$3\} rval"
return $rval
' language 'pltcl';
-- plpgsql version so we don't have to jump through hoops to call it from other functions
create function replace (varchar, varchar, varchar) returns varchar as '
declare
string alias for $1;
sub alias for $2;
replacement alias for $3;
-- xxxxxxxxxxx[MATCH]xxxxxxxxxxxx
-- | end_before
-- | start_after
match integer;
end_before integer;
start_after integer;
string_replaced varchar;
string_remainder varchar;
begin
string_remainder := string;
string_replaced := '''';
match := position(sub in string_remainder);
while match > 0 loop
end_before := match - 1;
start_after := match + length(sub);
string_replaced := string_replaced || substr(string_remainder, 1, end_b
efore) || replacement;
string_remainder := substr(string_remainder, start_after);
match := position(sub in string_remainder);
end loop;
string_replaced := string_replaced || string_remainder;
return string_replaced;
end;
' LANGUAGE 'plpgsql';
|