NexusDB Pty Ltd

Replace one string with another.

drop routine if exists StringReplace;
create function StringReplace(aStr varchar(50), 
findstr varchar(50),
replacestr varchar(50))
RETURNS varchar(50)
begin
  declare apos int;
  declare alen int;
  set apos = position(findstr in aStr);
  set alen = char_length(findstr);
  if replacestr is NULL then
    set replacestr = '';
    END if;
  while apos > 0 do
     set aStr = Substring(aStr from 1 for apos - 1) + replacestr +
     Substring(aStr from apos+alen for char_length(aStr)-alen-apos+1);
     set apos = position(findstr in aStr);
  end while;
  return aStr;
end;